<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Chapter 23 Stored Programs and Views</title>
<link rel="stylesheet" href="mvl.css" type="text/css" />
<meta name="generator" content="DocBook XSL Stylesheets + chunker.py v1.9.2" />
<link rel="start" href="index.html" title="{book-title}" />
<link rel="up" href="" title="" />
<link rel="prev" href="partitioning.html" title="Chapter 22 Partitioning" />
<link rel="next" href="information-schema.html" title="Chapter 24 INFORMATION_SCHEMA Tables" />
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF">
<div class="navheader">
<table width="100%" summary="Navigation header">
<tr>
<th colspan="3" align="center">Chapter 23 Stored Programs and Views</th>
</tr>
<tr>
<td width="20%" align="left"><a accesskey="p" href="partitioning.html">Prev</a> </td>
<th width="60%" align="center"></th>
<td width="20%" align="right"> <a accesskey="n" href="information-schema.html">Next</a></td>
</tr>
</table>
<hr>
</div>
<div class="chapter">
<div class="titlepage">
<div>
<div>
<h1 class="title"><a name="stored-programs-views"></a>Chapter 23 Stored Programs and Views</h1>

</div>

</div>

</div>
<div class="toc">
<p><b>Table of Contents</b></p><dl class="toc"><dt><span class="section"><a href="stored-programs-views.html#stored-programs-defining">23.1 Defining Stored Programs</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#stored-routines">23.2 Using Stored Routines (Procedures and Functions)</a></span></dt><dd><dl><dt><span class="section"><a href="stored-programs-views.html#stored-routines-syntax">23.2.1 Stored Routine Syntax</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#stored-routines-privileges">23.2.2 Stored Routines and MySQL Privileges</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#stored-routines-metadata">23.2.3 Stored Routine Metadata</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#stored-routines-last-insert-id">23.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()</a></span></dt></dl></dd><dt><span class="section"><a href="stored-programs-views.html#triggers">23.3 Using Triggers</a></span></dt><dd><dl><dt><span class="section"><a href="stored-programs-views.html#trigger-syntax">23.3.1 Trigger Syntax and Examples</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#trigger-metadata">23.3.2 Trigger Metadata</a></span></dt></dl></dd><dt><span class="section"><a href="stored-programs-views.html#event-scheduler">23.4 Using the Event Scheduler</a></span></dt><dd><dl><dt><span class="section"><a href="stored-programs-views.html#events-overview">23.4.1 Event Scheduler Overview</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#events-configuration">23.4.2 Event Scheduler Configuration</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#events-syntax">23.4.3 Event Syntax</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#events-metadata">23.4.4 Event Metadata</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#events-status-info">23.4.5 Event Scheduler Status</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#events-privileges">23.4.6 The Event Scheduler and MySQL Privileges</a></span></dt></dl></dd><dt><span class="section"><a href="stored-programs-views.html#views">23.5 Using Views</a></span></dt><dd><dl><dt><span class="section"><a href="stored-programs-views.html#view-syntax">23.5.1 View Syntax</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#view-algorithms">23.5.2 View Processing Algorithms</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#view-updatability">23.5.3 Updatable and Insertable Views</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#view-check-option">23.5.4 The View WITH CHECK OPTION Clause</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#view-metadata">23.5.5 View Metadata</a></span></dt></dl></dd><dt><span class="section"><a href="stored-programs-views.html#stored-programs-security">23.6 Access Control for Stored Programs and Views</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#stored-programs-logging">23.7 Binary Logging of Stored Programs</a></span></dt></dl>
</div>
<a class="indexterm" name="idm139684638356448"></a><a class="indexterm" name="idm139684638355408"></a><a class="indexterm" name="idm139684638353920"></a><a class="indexterm" name="idm139684638352848"></a><a class="indexterm" name="idm139684638351776"></a><a class="indexterm" name="idm139684638350704"></a><p>
    This chapter discusses stored programs and views, which are database
    objects defined in terms of SQL code that is stored on the server
    for later execution.
  </p><p>
    Stored programs include these objects:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        Stored routines, that is, stored procedures and functions. A
        stored procedure is invoked using the
        <a class="link" href="sql-syntax.html#call" title="13.2.1 CALL Syntax"><code class="literal">CALL</code></a> statement. A procedure does
        not have a return value but can modify its parameters for later
        inspection by the caller. It can also generate result sets to be
        returned to the client program. A stored function is used much
        like a built-in function. you invoke it in an expression and it
        returns a value during expression evaluation.
      </p></li><li class="listitem"><p>
        Triggers. A trigger is a named database object that is
        associated with a table and that is activated when a particular
        event occurs for the table, such as an insert or update.
      </p></li><li class="listitem"><p>
        Events. An event is a task that the server runs according to
        schedule.
</p></li></ul>
</div>
<p>
    Views are stored queries that when referenced produce a result set.
    A view acts as a virtual table.
  </p><p>
    This chapter describes how to use stored programs and views. The
    following sections provide additional information about SQL syntax
    for statements related to these objects:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        For each object type, there are <code class="literal">CREATE</code>,
        <code class="literal">ALTER</code>, and <code class="literal">DROP</code> statements
        that control which objects exist and how they are defined. See
        <a class="xref" href="sql-syntax.html#sql-syntax-data-definition" title="13.1 Data Definition Statements">Section 13.1, “Data Definition Statements”</a>.
      </p></li><li class="listitem"><p>
        The <a class="link" href="sql-syntax.html#call" title="13.2.1 CALL Syntax"><code class="literal">CALL</code></a> statement is used to
        invoke stored procedures. See <a class="xref" href="sql-syntax.html#call" title="13.2.1 CALL Syntax">Section 13.2.1, “CALL Syntax”</a>.
      </p></li><li class="listitem"><p>
        Stored program definitions include a body that may use compound
        statements, loops, conditionals, and declared variables. See
        <a class="xref" href="sql-syntax.html#sql-syntax-compound-statements" title="13.6 Compound-Statement Syntax">Section 13.6, “Compound-Statement Syntax”</a>.
</p></li></ul>
</div>
<p>
    In MySQL, metadata changes to objects referred to by stored programs
    are detected and cause automatic reparsing of the affected
    statements when the program is next executed. For more information,
    see <a class="xref" href="optimization.html#statement-caching" title="8.10.4 Caching of Prepared Statements and Stored Programs">Section 8.10.4, “Caching of Prepared Statements and Stored Programs”</a>.
</p>
<div class="section">

<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="stored-programs-defining"></a>23.1 Defining Stored Programs</h2>
</div>
</div>
</div>
<p>
      Each stored program contains a body that consists of an SQL
      statement. This statement may be a compound statement made up of
      several statements separated by semicolon (<code class="literal">;</code>)
      characters. For example, the following stored procedure has a body
      made up of a <a class="link" href="sql-syntax.html#begin-end" title="13.6.1 BEGIN ... END Compound-Statement Syntax"><code class="literal">BEGIN ...
      END</code></a> block that contains a
      <a class="link" href="sql-syntax.html#set-variable" title="13.7.4.1 SET Syntax for Variable Assignment"><code class="literal">SET</code></a>
      statement and a <a class="link" href="sql-syntax.html#repeat" title="13.6.5.6 REPEAT Syntax"><code class="literal">REPEAT</code></a> loop that
      itself contains another
      <a class="link" href="sql-syntax.html#set-variable" title="13.7.4.1 SET Syntax for Variable Assignment"><code class="literal">SET</code></a>
      statement:
    </p><pre data-lang="sql" class="programlisting">
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
  SET @x = 0;
  REPEAT SET @x = @x + 1; UNTIL @x &gt; p1 END REPEAT;
END;
</pre><p>
      If you use the <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> client program to define a
      stored program containing semicolon characters, a problem arises.
      By default, <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> itself recognizes the
      semicolon as a statement delimiter, so you must redefine the
      delimiter temporarily to cause <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> to pass
      the entire stored program definition to the server.
    </p><p>
      To redefine the <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> delimiter, use the
      <code class="literal">delimiter</code> command. The following example shows
      how to do this for the <code class="literal">dorepeat()</code> procedure
      just shown. The delimiter is changed to <code class="literal">//</code> to
      enable the entire definition to be passed to the server as a
      single statement, and then restored to <code class="literal">;</code> before
      invoking the procedure. This enables the <code class="literal">;</code>
      delimiter used in the procedure body to be passed through to the
      server rather than being interpreted by <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a>
      itself.
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>delimiter //</code></strong>

mysql&gt; <strong class="userinput"><code>CREATE PROCEDURE dorepeat(p1 INT)</code></strong>
    -&gt; <strong class="userinput"><code>BEGIN</code></strong>
    -&gt;   <strong class="userinput"><code>SET @x = 0;</code></strong>
    -&gt;   <strong class="userinput"><code>REPEAT SET @x = @x + 1; UNTIL @x &gt; p1 END REPEAT;</code></strong>
    -&gt; <strong class="userinput"><code>END</code></strong>
    -&gt; <strong class="userinput"><code>//</code></strong>
Query OK, 0 rows affected (0.00 sec)

mysql&gt; <strong class="userinput"><code>delimiter ;</code></strong>

mysql&gt; <strong class="userinput"><code>CALL dorepeat(1000);</code></strong>
Query OK, 0 rows affected (0.00 sec)

mysql&gt; <strong class="userinput"><code>SELECT @x;</code></strong>
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)
</pre><p>
      You can redefine the delimiter to a string other than
      <code class="literal">//</code>, and the delimiter can consist of a single
      character or multiple characters. You should avoid the use of the
      backslash (<code class="literal">\</code>) character because that is the
      escape character for MySQL.
    </p><p>
      The following is an example of a function that takes a parameter,
      performs an operation using an SQL function, and returns the
      result. In this case, it is unnecessary to use
      <code class="literal">delimiter</code> because the function definition
      contains no internal <code class="literal">;</code> statement delimiters:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE FUNCTION hello (s CHAR(20))</code></strong>
mysql&gt; <strong class="userinput"><code>RETURNS CHAR(50) DETERMINISTIC</code></strong>
    -&gt; <strong class="userinput"><code>RETURN CONCAT('Hello, ',s,'!');</code></strong>
Query OK, 0 rows affected (0.00 sec)

mysql&gt; <strong class="userinput"><code>SELECT hello('world');</code></strong>
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)
</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="stored-routines"></a>23.2 Using Stored Routines (Procedures and Functions)</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="stored-programs-views.html#stored-routines-syntax">23.2.1 Stored Routine Syntax</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#stored-routines-privileges">23.2.2 Stored Routines and MySQL Privileges</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#stored-routines-metadata">23.2.3 Stored Routine Metadata</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#stored-routines-last-insert-id">23.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()</a></span></dt></dl>
</div>
<a class="indexterm" name="idm139684638295488"></a><a class="indexterm" name="idm139684638294448"></a><a class="indexterm" name="idm139684638292960"></a><a class="indexterm" name="idm139684638291888"></a><p>
    MySQL supports stored routines (procedures and functions). A stored
    routine is a set of SQL statements that can be stored in the server.
    Once this has been done, clients don't need to keep reissuing the
    individual statements but can refer to the stored routine instead.
  </p><p>
    Stored routines require the <code class="literal">proc</code> table in the
    <code class="literal">mysql</code> database. This table is created during the
    MySQL installation procedure. If you are upgrading to MySQL
    5.7 from an earlier version, be sure to update your
    grant tables to make sure that the <code class="literal">proc</code> table
    exists. See <a class="xref" href="programs.html#mysql-upgrade" title="4.4.7 mysql_upgrade — Check and Upgrade MySQL Tables">Section 4.4.7, “<span class="command"><strong>mysql_upgrade</strong></span> — Check and Upgrade MySQL Tables”</a>.
  </p><p>
    Stored routines can be particularly useful in certain situations:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        When multiple client applications are written in different
        languages or work on different platforms, but need to perform
        the same database operations.
      </p></li><li class="listitem"><p>
        When security is paramount. Banks, for example, use stored
        procedures and functions for all common operations. This
        provides a consistent and secure environment, and routines can
        ensure that each operation is properly logged. In such a setup,
        applications and users would have no access to the database
        tables directly, but can only execute specific stored routines.
</p></li></ul>
</div>
<p>
    Stored routines can provide improved performance because less
    information needs to be sent between the server and the client. The
    tradeoff is that this does increase the load on the database server
    because more of the work is done on the server side and less is done
    on the client (application) side. Consider this if many client
    machines (such as Web servers) are serviced by only one or a few
    database servers.
  </p><p>
    Stored routines also enable you to have libraries of functions in
    the database server. This is a feature shared by modern application
    languages that enable such design internally (for example, by using
    classes). Using these client application language features is
    beneficial for the programmer even outside the scope of database
    use.
  </p><p>
    MySQL follows the SQL:2003 syntax for stored routines, which is also
    used by IBM's DB2. All syntax described here is supported and any
    limitations and extensions are documented where appropriate.
</p>
<h3><a name="idm139684638281008"></a>Additional Resources</h3>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        You may find the <a class="ulink" href="http://forums.mysql.com/list.php?98" target="_top">Stored
        Procedures User Forum</a> of use when working with stored
        procedures and functions.
      </p></li><li class="listitem"><p>
        For answers to some commonly asked questions regarding stored
        routines in MySQL, see <a class="xref" href="faqs.html#faqs-stored-procs" title="A.4 MySQL 5.7 FAQ: Stored Procedures and Functions">Section A.4, “MySQL 5.7 FAQ: Stored Procedures and Functions”</a>.
      </p></li><li class="listitem"><p>
        There are some restrictions on the use of stored routines. See
        <a class="xref" href="restrictions.html#stored-program-restrictions" title="C.1 Restrictions on Stored Programs">Section C.1, “Restrictions on Stored Programs”</a>.
      </p></li><li class="listitem"><p>
        Binary logging for stored routines takes place as described in
        <a class="xref" href="stored-programs-views.html#stored-programs-logging" title="23.7 Binary Logging of Stored Programs">Section 23.7, “Binary Logging of Stored Programs”</a>.
</p></li></ul>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="stored-routines-syntax"></a>23.2.1 Stored Routine Syntax</h3>

</div>

</div>

</div>
<p>
      A stored routine is either a procedure or a function. Stored
      routines are created with the <a class="link" href="sql-syntax.html#create-procedure" title="13.1.16 CREATE PROCEDURE and CREATE FUNCTION Syntax"><code class="literal">CREATE
      PROCEDURE</code></a> and <a class="link" href="sql-syntax.html#create-function" title="13.1.13 CREATE FUNCTION Syntax"><code class="literal">CREATE
      FUNCTION</code></a> statements (see
      <a class="xref" href="sql-syntax.html#create-procedure" title="13.1.16 CREATE PROCEDURE and CREATE FUNCTION Syntax">Section 13.1.16, “CREATE PROCEDURE and CREATE FUNCTION Syntax”</a>). A procedure is invoked using
      a <a class="link" href="sql-syntax.html#call" title="13.2.1 CALL Syntax"><code class="literal">CALL</code></a> statement (see
      <a class="xref" href="sql-syntax.html#call" title="13.2.1 CALL Syntax">Section 13.2.1, “CALL Syntax”</a>), and can only pass back values using
      output variables. A function can be called from inside a statement
      just like any other function (that is, by invoking the function's
      name), and can return a scalar value. The body of a stored routine
      can use compound statements (see
      <a class="xref" href="sql-syntax.html#sql-syntax-compound-statements" title="13.6 Compound-Statement Syntax">Section 13.6, “Compound-Statement Syntax”</a>).
    </p><p>
      Stored routines can be dropped with the <a class="link" href="sql-syntax.html#drop-procedure" title="13.1.27 DROP PROCEDURE and DROP FUNCTION Syntax"><code class="literal">DROP
      PROCEDURE</code></a> and <a class="link" href="sql-syntax.html#drop-function" title="13.1.24 DROP FUNCTION Syntax"><code class="literal">DROP
      FUNCTION</code></a> statements (see
      <a class="xref" href="sql-syntax.html#drop-procedure" title="13.1.27 DROP PROCEDURE and DROP FUNCTION Syntax">Section 13.1.27, “DROP PROCEDURE and DROP FUNCTION Syntax”</a>), and altered with the
      <a class="link" href="sql-syntax.html#alter-procedure" title="13.1.6 ALTER PROCEDURE Syntax"><code class="literal">ALTER PROCEDURE</code></a> and
      <a class="link" href="sql-syntax.html#alter-function" title="13.1.3 ALTER FUNCTION Syntax"><code class="literal">ALTER FUNCTION</code></a> statements (see
      <a class="xref" href="sql-syntax.html#alter-procedure" title="13.1.6 ALTER PROCEDURE Syntax">Section 13.1.6, “ALTER PROCEDURE Syntax”</a>).
    </p><p>
      A stored procedure or function is associated with a particular
      database. This has several implications:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          When the routine is invoked, an implicit <code class="literal">USE
          <em class="replaceable"><code>db_name</code></em></code> is performed (and
          undone when the routine terminates).
          <a class="link" href="sql-syntax.html#use" title="13.8.4 USE Syntax"><code class="literal">USE</code></a> statements within stored
          routines are not permitted.
        </p></li><li class="listitem"><p>
          You can qualify routine names with the database name. This can
          be used to refer to a routine that is not in the current
          database. For example, to invoke a stored procedure
          <code class="literal">p</code> or function <code class="literal">f</code> that is
          associated with the <code class="literal">test</code> database, you can
          say <code class="literal">CALL test.p()</code> or
          <code class="literal">test.f()</code>.
        </p></li><li class="listitem"><p>
          When a database is dropped, all stored routines associated
          with it are dropped as well.
</p></li></ul>
</div>
<p>
      Stored functions cannot be recursive.
    </p><p>
      Recursion in stored procedures is permitted but disabled by
      default. To enable recursion, set the
      <a class="link" href="server-administration.html#sysvar_max_sp_recursion_depth"><code class="literal">max_sp_recursion_depth</code></a> server
      system variable to a value greater than zero. Stored procedure
      recursion increases the demand on thread stack space. If you
      increase the value of
      <a class="link" href="server-administration.html#sysvar_max_sp_recursion_depth"><code class="literal">max_sp_recursion_depth</code></a>, it may be
      necessary to increase thread stack size by increasing the value of
      <a class="link" href="server-administration.html#sysvar_thread_stack"><code class="literal">thread_stack</code></a> at server startup.
      See <a class="xref" href="server-administration.html#server-system-variables" title="5.1.7 Server System Variables">Section 5.1.7, “Server System Variables”</a>, for more
      information.
    </p><p>
      MySQL supports a very useful extension that enables the use of
      regular <a class="link" href="sql-syntax.html#select" title="13.2.9 SELECT Syntax"><code class="literal">SELECT</code></a> statements (that is,
      without using cursors or local variables) inside a stored
      procedure. The result set of such a query is simply sent directly
      to the client. Multiple <a class="link" href="sql-syntax.html#select" title="13.2.9 SELECT Syntax"><code class="literal">SELECT</code></a>
      statements generate multiple result sets, so the client must use a
      MySQL client library that supports multiple result sets. This
      means the client must use a client library from a version of MySQL
      at least as recent as 4.1. The client should also specify the
      <code class="literal">CLIENT_MULTI_RESULTS</code> option when it connects.
      For C programs, this can be done with the
      <a class="link" href="connectors-apis.html#mysql-real-connect" title="27.8.7.54 mysql_real_connect()"><code class="literal">mysql_real_connect()</code></a> C API
      function. See <a class="xref" href="connectors-apis.html#mysql-real-connect" title="27.8.7.54 mysql_real_connect()">Section 27.8.7.54, “mysql_real_connect()”</a>, and
      <a class="xref" href="connectors-apis.html#c-api-multiple-queries" title="27.8.16 C API Multiple Statement Execution Support">Section 27.8.16, “C API Multiple Statement Execution Support”</a>.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="stored-routines-privileges"></a>23.2.2 Stored Routines and MySQL Privileges</h3>

</div>

</div>

</div>
<p>
      The MySQL grant system takes stored routines into account as
      follows:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          The <a class="link" href="security.html#priv_create-routine"><code class="literal">CREATE ROUTINE</code></a> privilege is
          needed to create stored routines.
        </p></li><li class="listitem"><p>
          The <a class="link" href="security.html#priv_alter-routine"><code class="literal">ALTER ROUTINE</code></a> privilege is
          needed to alter or drop stored routines. This privilege is
          granted automatically to the creator of a routine if
          necessary, and dropped from the creator when the routine is
          dropped.
        </p></li><li class="listitem"><p>
          The <a class="link" href="security.html#priv_execute"><code class="literal">EXECUTE</code></a> privilege is
          required to execute stored routines. However, this privilege
          is granted automatically to the creator of a routine if
          necessary (and dropped from the creator when the routine is
          dropped). Also, the default <code class="literal">SQL SECURITY</code>
          characteristic for a routine is <code class="literal">DEFINER</code>,
          which enables users who have access to the database with which
          the routine is associated to execute the routine.
        </p></li><li class="listitem"><p>
          If the
          <a class="link" href="server-administration.html#sysvar_automatic_sp_privileges"><code class="literal">automatic_sp_privileges</code></a>
          system variable is 0, the
          <a class="link" href="security.html#priv_execute"><code class="literal">EXECUTE</code></a> and
          <a class="link" href="security.html#priv_alter-routine"><code class="literal">ALTER ROUTINE</code></a> privileges are
          not automatically granted to and dropped from the routine
          creator.
        </p></li><li class="listitem"><p>
          The creator of a routine is the account used to execute the
          <code class="literal">CREATE</code> statement for it. This might not be
          the same as the account named as the
          <code class="literal">DEFINER</code> in the routine definition.
</p></li></ul>
</div>
<p>
      The server manipulates the <code class="literal">mysql.proc</code> table in
      response to statements that create, alter, or drop stored
      routines. It is not supported that the server will notice manual
      manipulation of this table.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="stored-routines-metadata"></a>23.2.3 Stored Routine Metadata</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm139684638217088"></a><a class="indexterm" name="idm139684638215632"></a><p>
      Metadata about stored routines can be obtained as follows:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Query the <a class="link" href="information-schema.html#routines-table" title="24.21 The INFORMATION_SCHEMA ROUTINES Table"><code class="literal">ROUTINES</code></a> table of the
          <code class="literal">INFORMATION_SCHEMA</code> database. See
          <a class="xref" href="information-schema.html#routines-table" title="24.21 The INFORMATION_SCHEMA ROUTINES Table">Section 24.21, “The INFORMATION_SCHEMA ROUTINES Table”</a>.
        </p></li><li class="listitem"><p>
          Use the <a class="link" href="sql-syntax.html#show-create-procedure" title="13.7.5.9 SHOW CREATE PROCEDURE Syntax"><code class="literal">SHOW CREATE PROCEDURE</code></a>
          and <a class="link" href="sql-syntax.html#show-create-function" title="13.7.5.8 SHOW CREATE FUNCTION Syntax"><code class="literal">SHOW CREATE FUNCTION</code></a>
          statements to see routine definitions. See
          <a class="xref" href="sql-syntax.html#show-create-procedure" title="13.7.5.9 SHOW CREATE PROCEDURE Syntax">Section 13.7.5.9, “SHOW CREATE PROCEDURE Syntax”</a>.
        </p></li><li class="listitem"><p>
          Use the <a class="link" href="sql-syntax.html#show-procedure-status" title="13.7.5.28 SHOW PROCEDURE STATUS Syntax"><code class="literal">SHOW PROCEDURE STATUS</code></a>
          and <a class="link" href="sql-syntax.html#show-function-status" title="13.7.5.20 SHOW FUNCTION STATUS Syntax"><code class="literal">SHOW FUNCTION STATUS</code></a>
          statements to see routine characteristics. See
          <a class="xref" href="sql-syntax.html#show-procedure-status" title="13.7.5.28 SHOW PROCEDURE STATUS Syntax">Section 13.7.5.28, “SHOW PROCEDURE STATUS Syntax”</a>.
</p></li></ul>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="stored-routines-last-insert-id"></a>23.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm139684638200464"></a><a class="indexterm" name="idm139684638198976"></a><a class="indexterm" name="idm139684638197488"></a><a class="indexterm" name="idm139684638196000"></a><p>
      Within the body of a stored routine (procedure or function) or a
      trigger, the value of
      <a class="link" href="functions.html#function_last-insert-id"><code class="literal">LAST_INSERT_ID()</code></a> changes the same
      way as for statements executed outside the body of these kinds of
      objects (see <a class="xref" href="functions.html#information-functions" title="12.14 Information Functions">Section 12.14, “Information Functions”</a>). The effect
      of a stored routine or trigger upon the value of
      <a class="link" href="functions.html#function_last-insert-id"><code class="literal">LAST_INSERT_ID()</code></a> that is seen by
      following statements depends on the kind of routine:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          If a stored procedure executes statements that change the
          value of <a class="link" href="functions.html#function_last-insert-id"><code class="literal">LAST_INSERT_ID()</code></a>, the
          changed value is seen by statements that follow the procedure
          call.
        </p></li><li class="listitem"><p>
          For stored functions and triggers that change the value, the
          value is restored when the function or trigger ends, so
          following statements do not see a changed value.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="triggers"></a>23.3 Using Triggers</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="stored-programs-views.html#trigger-syntax">23.3.1 Trigger Syntax and Examples</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#trigger-metadata">23.3.2 Trigger Metadata</a></span></dt></dl>
</div>
<a class="indexterm" name="idm139684638185760"></a><p>
    A trigger is a named database object that is associated with a
    table, and that activates when a particular event occurs for the
    table. Some uses for triggers are to perform checks of values to be
    inserted into a table or to perform calculations on values involved
    in an update.
  </p><p>
    A trigger is defined to activate when a statement inserts, updates,
    or deletes rows in the associated table. These row operations are
    trigger events. For example, rows can be inserted by
    <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a> or <a class="link" href="sql-syntax.html#load-data" title="13.2.6 LOAD DATA INFILE Syntax"><code class="literal">LOAD
    DATA</code></a> statements, and an insert trigger activates for each
    inserted row. A trigger can be set to activate either before or
    after the trigger event. For example, you can have a trigger
    activate before each row that is inserted into a table or after each
    row that is updated.
</p>
<div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Important
</div>
<p>
      MySQL triggers activate only for changes made to tables by SQL
      statements. This includes changes to base tables that underlie
      updatable views. Triggers do not activate for changes to tables
      made by APIs that do not transmit SQL statements to the MySQL
      Server. This means that triggers are not activated by updates made
      using the <a class="link" href="mysql-cluster.html" title="Chapter 21 MySQL NDB Cluster 7.5 and NDB Cluster 7.6"><code class="literal">NDB</code></a> API.
    </p><p>
      Triggers are not activated by changes in
      <code class="literal">INFORMATION_SCHEMA</code> or
      <code class="literal">performance_schema</code> tables. Those tables are
      actually views and triggers are not permitted on views.
</p>
</div>
<p>
    The following sections describe the syntax for creating and dropping
    triggers, show some examples of how to use them, and indicate how to
    obtain trigger metadata.
</p>
<h3><a name="idm139684638175744"></a>Additional Resources</h3>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        You may find the <a class="ulink" href="http://forums.mysql.com/list.php?100" target="_top">Triggers
        User Forum</a> of use when working with triggers.
      </p></li><li class="listitem"><p>
        For answers to commonly asked questions regarding triggers in
        MySQL, see <a class="xref" href="faqs.html#faqs-triggers" title="A.5 MySQL 5.7 FAQ: Triggers">Section A.5, “MySQL 5.7 FAQ: Triggers”</a>.
      </p></li><li class="listitem"><p>
        There are some restrictions on the use of triggers; see
        <a class="xref" href="restrictions.html#stored-program-restrictions" title="C.1 Restrictions on Stored Programs">Section C.1, “Restrictions on Stored Programs”</a>.
      </p></li><li class="listitem"><p>
        Binary logging for triggers takes place as described in
        <a class="xref" href="stored-programs-views.html#stored-programs-logging" title="23.7 Binary Logging of Stored Programs">Section 23.7, “Binary Logging of Stored Programs”</a>.
</p></li></ul>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="trigger-syntax"></a>23.3.1 Trigger Syntax and Examples</h3>

</div>

</div>

</div>
<p>
      To create a trigger or drop a trigger, use the
      <a class="link" href="sql-syntax.html#create-trigger" title="13.1.20 CREATE TRIGGER Syntax"><code class="literal">CREATE TRIGGER</code></a> or
      <a class="link" href="sql-syntax.html#drop-trigger" title="13.1.31 DROP TRIGGER Syntax"><code class="literal">DROP TRIGGER</code></a> statement, described
      in <a class="xref" href="sql-syntax.html#create-trigger" title="13.1.20 CREATE TRIGGER Syntax">Section 13.1.20, “CREATE TRIGGER Syntax”</a>, and
      <a class="xref" href="sql-syntax.html#drop-trigger" title="13.1.31 DROP TRIGGER Syntax">Section 13.1.31, “DROP TRIGGER Syntax”</a>.
    </p><p>
      Here is a simple example that associates a trigger with a table,
      to activate for <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a> operations.
      The trigger acts as an accumulator, summing the values inserted
      into one of the columns of the table.
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));</code></strong>
Query OK, 0 rows affected (0.03 sec)

mysql&gt; <strong class="userinput"><code>CREATE TRIGGER ins_sum BEFORE INSERT ON account</code></strong>
       <strong class="userinput"><code>FOR EACH ROW SET @sum = @sum + NEW.amount;</code></strong>
Query OK, 0 rows affected (0.01 sec)
</pre><p>
      The <a class="link" href="sql-syntax.html#create-trigger" title="13.1.20 CREATE TRIGGER Syntax"><code class="literal">CREATE TRIGGER</code></a> statement
      creates a trigger named <code class="literal">ins_sum</code> that is
      associated with the <code class="literal">account</code> table. It also
      includes clauses that specify the trigger action time, the
      triggering event, and what to do when the trigger activates:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          The keyword <code class="literal">BEFORE</code> indicates the trigger
          action time. In this case, the trigger activates before each
          row inserted into the table. The other permitted keyword here
          is <code class="literal">AFTER</code>.
        </p></li><li class="listitem"><p>
          The keyword <code class="literal">INSERT</code> indicates the trigger
          event; that is, the type of operation that activates the
          trigger. In the example, <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a>
          operations cause trigger activation. You can also create
          triggers for <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a> and
          <a class="link" href="sql-syntax.html#update" title="13.2.11 UPDATE Syntax"><code class="literal">UPDATE</code></a> operations.
        </p></li><li class="listitem"><p>
          The statement following <code class="literal">FOR EACH ROW</code>
          defines the trigger body; that is, the statement to execute
          each time the trigger activates, which occurs once for each
          row affected by the triggering event. In the example, the
          trigger body is a simple
          <a class="link" href="sql-syntax.html#set-variable" title="13.7.4.1 SET Syntax for Variable Assignment"><code class="literal">SET</code></a>
          that accumulates into a user variable the values inserted into
          the <code class="literal">amount</code> column. The statement refers to
          the column as <code class="literal">NEW.amount</code> which means
          <span class="quote">“<span class="quote">the value of the <code class="literal">amount</code> column to be
          inserted into the new row.</span>”</span>
</p></li></ul>
</div>
<p>
      To use the trigger, set the accumulator variable to zero, execute
      an <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a> statement, and then see
      what value the variable has afterward:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SET @sum = 0;</code></strong>
mysql&gt; <strong class="userinput"><code>INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);</code></strong>
mysql&gt; <strong class="userinput"><code>SELECT @sum AS 'Total amount inserted';</code></strong>
+-----------------------+
| Total amount inserted |
+-----------------------+
|               1852.48 |
+-----------------------+
</pre><p>
      In this case, the value of <code class="literal">@sum</code> after the
      <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a> statement has executed is
      <code class="literal">14.98 + 1937.50 - 100</code>, or
      <code class="literal">1852.48</code>.
    </p><p>
      To destroy the trigger, use a <a class="link" href="sql-syntax.html#drop-trigger" title="13.1.31 DROP TRIGGER Syntax"><code class="literal">DROP
      TRIGGER</code></a> statement. You must specify the schema name if
      the trigger is not in the default schema:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>DROP TRIGGER test.ins_sum;</code></strong>
</pre><p>
      If you drop a table, any triggers for the table are also dropped.
    </p><p>
      Trigger names exist in the schema namespace, meaning that all
      triggers must have unique names within a schema. Triggers in
      different schemas can have the same name.
    </p><p>
      As of MySQL 5.7.2, it is possible to define multiple triggers for
      a given table that have the same trigger event and action time.
      For example, you can have two <code class="literal">BEFORE UPDATE</code>
      triggers for a table. By default, triggers that have the same
      trigger event and action time activate in the order they were
      created. To affect trigger order, specify a clause after
      <code class="literal">FOR EACH ROW</code> that indicates
      <code class="literal">FOLLOWS</code> or <code class="literal">PRECEDES</code> and the
      name of an existing trigger that also has the same trigger event
      and action time. With <code class="literal">FOLLOWS</code>, the new trigger
      activates after the existing trigger. With
      <code class="literal">PRECEDES</code>, the new trigger activates before the
      existing trigger.
    </p><p>
      For example, the following trigger definition defines another
      <code class="literal">BEFORE INSERT</code> trigger for the
      <code class="literal">account</code> table:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE TRIGGER ins_transaction BEFORE INSERT ON account</code></strong>
       <strong class="userinput"><code>FOR EACH ROW PRECEDES ins_sum</code></strong>
       <strong class="userinput"><code>SET</code></strong>
       <strong class="userinput"><code>@deposits = @deposits + IF(NEW.amount&gt;0,NEW.amount,0),</code></strong>
       <strong class="userinput"><code>@withdrawals = @withdrawals + IF(NEW.amount&lt;0,-NEW.amount,0);</code></strong>
Query OK, 0 rows affected (0.01 sec)
</pre><p>
      This trigger, <code class="literal">ins_transaction</code>, is similar to
      <code class="literal">ins_sum</code> but accumulates deposits and
      withdrawals separately. It has a <code class="literal">PRECEDES</code>
      clause that causes it to activate before
      <code class="literal">ins_sum</code>; without that clause, it would activate
      after <code class="literal">ins_sum</code> because it is created after
      <code class="literal">ins_sum</code>.
    </p><p>
      Before MySQL 5.7.2, there cannot be multiple triggers for a given
      table that have the same trigger event and action time. For
      example, you cannot have two <code class="literal">BEFORE UPDATE</code>
      triggers for a table. To work around this, you can define a
      trigger that executes multiple statements by using the
      <a class="link" href="sql-syntax.html#begin-end" title="13.6.1 BEGIN ... END Compound-Statement Syntax"><code class="literal">BEGIN ... END</code></a>
      compound statement construct after <code class="literal">FOR EACH
      ROW</code>. (An example appears later in this section.)
    </p><p>
      Within the trigger body, the <code class="literal">OLD</code> and
      <code class="literal">NEW</code> keywords enable you to access columns in
      the rows affected by a trigger. <code class="literal">OLD</code> and
      <code class="literal">NEW</code> are MySQL extensions to triggers; they are
      not case-sensitive.
    </p><p>
      In an <code class="literal">INSERT</code> trigger, only
      <code class="literal">NEW.<em class="replaceable"><code>col_name</code></em></code> can be
      used; there is no old row. In a <code class="literal">DELETE</code> trigger,
      only <code class="literal">OLD.<em class="replaceable"><code>col_name</code></em></code>
      can be used; there is no new row. In an <code class="literal">UPDATE</code>
      trigger, you can use
      <code class="literal">OLD.<em class="replaceable"><code>col_name</code></em></code> to
      refer to the columns of a row before it is updated and
      <code class="literal">NEW.<em class="replaceable"><code>col_name</code></em></code> to
      refer to the columns of the row after it is updated.
    </p><p>
      A column named with <code class="literal">OLD</code> is read only. You can
      refer to it (if you have the <a class="link" href="sql-syntax.html#select" title="13.2.9 SELECT Syntax"><code class="literal">SELECT</code></a>
      privilege), but not modify it. You can refer to a column named
      with <code class="literal">NEW</code> if you have the
      <a class="link" href="security.html#priv_select"><code class="literal">SELECT</code></a> privilege for it. In a
      <code class="literal">BEFORE</code> trigger, you can also change its value
      with <code class="literal">SET NEW.<em class="replaceable"><code>col_name</code></em> =
      <em class="replaceable"><code>value</code></em></code> if you have the
      <a class="link" href="security.html#priv_update"><code class="literal">UPDATE</code></a> privilege for it. This means
      you can use a trigger to modify the values to be inserted into a
      new row or used to update a row. (Such a <code class="literal">SET</code>
      statement has no effect in an <code class="literal">AFTER</code> trigger
      because the row change will have already occurred.)
    </p><p>
      In a <code class="literal">BEFORE</code> trigger, the <code class="literal">NEW</code>
      value for an <code class="literal">AUTO_INCREMENT</code> column is 0, not
      the sequence number that is generated automatically when the new
      row actually is inserted.
    </p><p>
      By using the <a class="link" href="sql-syntax.html#begin-end" title="13.6.1 BEGIN ... END Compound-Statement Syntax"><code class="literal">BEGIN ...
      END</code></a> construct, you can define a trigger that executes
      multiple statements. Within the <code class="literal">BEGIN</code> block,
      you also can use other syntax that is permitted within stored
      routines such as conditionals and loops. However, just as for
      stored routines, if you use the <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> program
      to define a trigger that executes multiple statements, it is
      necessary to redefine the <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a> statement
      delimiter so that you can use the <code class="literal">;</code> statement
      delimiter within the trigger definition. The following example
      illustrates these points. It defines an <code class="literal">UPDATE</code>
      trigger that checks the new value to be used for updating each
      row, and modifies the value to be within the range from 0 to 100.
      This must be a <code class="literal">BEFORE</code> trigger because the value
      must be checked before it is used to update the row:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>delimiter //</code></strong>
mysql&gt; <strong class="userinput"><code>CREATE TRIGGER upd_check BEFORE UPDATE ON account</code></strong>
       <strong class="userinput"><code>FOR EACH ROW</code></strong>
       <strong class="userinput"><code>BEGIN</code></strong>
           <strong class="userinput"><code>IF NEW.amount &lt; 0 THEN</code></strong>
               <strong class="userinput"><code>SET NEW.amount = 0;</code></strong>
           <strong class="userinput"><code>ELSEIF NEW.amount &gt; 100 THEN</code></strong>
               <strong class="userinput"><code>SET NEW.amount = 100;</code></strong>
           <strong class="userinput"><code>END IF;</code></strong>
       <strong class="userinput"><code>END;//</code></strong>
mysql&gt; <strong class="userinput"><code>delimiter ;</code></strong>
</pre><p>
      It can be easier to define a stored procedure separately and then
      invoke it from the trigger using a simple
      <a class="link" href="sql-syntax.html#call" title="13.2.1 CALL Syntax"><code class="literal">CALL</code></a> statement. This is also
      advantageous if you want to execute the same code from within
      several triggers.
    </p><p>
      There are limitations on what can appear in statements that a
      trigger executes when activated:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          The trigger cannot use the <a class="link" href="sql-syntax.html#call" title="13.2.1 CALL Syntax"><code class="literal">CALL</code></a>
          statement to invoke stored procedures that return data to the
          client or that use dynamic SQL. (Stored procedures are
          permitted to return data to the trigger through
          <code class="literal">OUT</code> or <code class="literal">INOUT</code>
          parameters.)
        </p></li><li class="listitem"><p>
          The trigger cannot use statements that explicitly or
          implicitly begin or end a transaction, such as
          <a class="link" href="sql-syntax.html#commit" title="13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax"><code class="literal">START
          TRANSACTION</code></a>, <a class="link" href="sql-syntax.html#commit" title="13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax"><code class="literal">COMMIT</code></a>,
          or <a class="link" href="sql-syntax.html#commit" title="13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax"><code class="literal">ROLLBACK</code></a>.
          (<a class="link" href="sql-syntax.html#commit" title="13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax"><code class="literal">ROLLBACK to
          SAVEPOINT</code></a> is permitted because it does not end a
          transaction.).
</p></li></ul>
</div>
<p>
      See also <a class="xref" href="restrictions.html#stored-program-restrictions" title="C.1 Restrictions on Stored Programs">Section C.1, “Restrictions on Stored Programs”</a>.
    </p><p>
      MySQL handles errors during trigger execution as follows:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          If a <code class="literal">BEFORE</code> trigger fails, the operation on
          the corresponding row is not performed.
        </p></li><li class="listitem"><p>
          A <code class="literal">BEFORE</code> trigger is activated by the
          <span class="emphasis"><em>attempt</em></span> to insert or modify the row,
          regardless of whether the attempt subsequently succeeds.
        </p></li><li class="listitem"><p>
          An <code class="literal">AFTER</code> trigger is executed only if any
          <code class="literal">BEFORE</code> triggers and the row operation
          execute successfully.
        </p></li><li class="listitem"><p>
          An error during either a <code class="literal">BEFORE</code> or
          <code class="literal">AFTER</code> trigger results in failure of the
          entire statement that caused trigger invocation.
        </p></li><li class="listitem"><p>
          For transactional tables, failure of a statement should cause
          rollback of all changes performed by the statement. Failure of
          a trigger causes the statement to fail, so trigger failure
          also causes rollback. For nontransactional tables, such
          rollback cannot be done, so although the statement fails, any
          changes performed prior to the point of the error remain in
          effect.
</p></li></ul>
</div>
<p>
      Triggers can contain direct references to tables by name, such as
      the trigger named <code class="literal">testref</code> shown in this
      example:
    </p><pre data-lang="sql" class="programlisting">
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  b4 INT DEFAULT 0
);

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW
  BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;
|

delimiter ;

INSERT INTO test3 (a3) VALUES
  (NULL), (NULL), (NULL), (NULL), (NULL),
  (NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES
  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
</pre><p>
      Suppose that you insert the following values into table
      <code class="literal">test1</code> as shown here:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>INSERT INTO test1 VALUES </code></strong>
       <strong class="userinput"><code>(1), (3), (1), (7), (1), (8), (4), (4);</code></strong>
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0
</pre><p>
      As a result, the four tables contain the following data:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM test1;</code></strong>
+------+
| a1   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql&gt; <strong class="userinput"><code>SELECT * FROM test2;</code></strong>
+------+
| a2   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql&gt; <strong class="userinput"><code>SELECT * FROM test3;</code></strong>
+----+
| a3 |
+----+
|  2 |
|  5 |
|  6 |
|  9 |
| 10 |
+----+
5 rows in set (0.00 sec)

mysql&gt; <strong class="userinput"><code>SELECT * FROM test4;</code></strong>
+----+------+
| a4 | b4   |
+----+------+
|  1 |    3 |
|  2 |    0 |
|  3 |    1 |
|  4 |    2 |
|  5 |    0 |
|  6 |    0 |
|  7 |    1 |
|  8 |    1 |
|  9 |    0 |
| 10 |    0 |
+----+------+
10 rows in set (0.00 sec)
</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="trigger-metadata"></a>23.3.2 Trigger Metadata</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm139684638031952"></a><a class="indexterm" name="idm139684638030464"></a><p>
      Metadata about triggers can be obtained as follows:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Query the <a class="link" href="information-schema.html#triggers-table" title="24.29 The INFORMATION_SCHEMA TRIGGERS Table"><code class="literal">TRIGGERS</code></a> table of the
          <code class="literal">INFORMATION_SCHEMA</code> database. See
          <a class="xref" href="information-schema.html#triggers-table" title="24.29 The INFORMATION_SCHEMA TRIGGERS Table">Section 24.29, “The INFORMATION_SCHEMA TRIGGERS Table”</a>.
        </p></li><li class="listitem"><p>
          Use the <a class="link" href="sql-syntax.html#show-create-trigger" title="13.7.5.11 SHOW CREATE TRIGGER Syntax"><code class="literal">SHOW CREATE TRIGGER</code></a>
          statement. See <a class="xref" href="sql-syntax.html#show-create-trigger" title="13.7.5.11 SHOW CREATE TRIGGER Syntax">Section 13.7.5.11, “SHOW CREATE TRIGGER Syntax”</a>.
        </p></li><li class="listitem"><p>
          Use the <a class="link" href="sql-syntax.html#show-triggers" title="13.7.5.38 SHOW TRIGGERS Syntax"><code class="literal">SHOW TRIGGERS</code></a>
          statement. See <a class="xref" href="sql-syntax.html#show-triggers" title="13.7.5.38 SHOW TRIGGERS Syntax">Section 13.7.5.38, “SHOW TRIGGERS Syntax”</a>.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="event-scheduler"></a>23.4 Using the Event Scheduler</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="stored-programs-views.html#events-overview">23.4.1 Event Scheduler Overview</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#events-configuration">23.4.2 Event Scheduler Configuration</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#events-syntax">23.4.3 Event Syntax</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#events-metadata">23.4.4 Event Metadata</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#events-status-info">23.4.5 Event Scheduler Status</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#events-privileges">23.4.6 The Event Scheduler and MySQL Privileges</a></span></dt></dl>
</div>
<a class="indexterm" name="idm139684638017824"></a><a class="indexterm" name="idm139684638016784"></a><p>
    The <span class="firstterm">MySQL Event Scheduler</span>
    manages the scheduling and execution of events, that is, tasks that
    run according to a schedule. The following discussion covers the
    Event Scheduler and is divided into the following sections:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        <a class="xref" href="stored-programs-views.html#events-overview" title="23.4.1 Event Scheduler Overview">Section 23.4.1, “Event Scheduler Overview”</a>, provides an introduction to
        and conceptual overview of MySQL Events.
      </p></li><li class="listitem"><p>
        <a class="xref" href="stored-programs-views.html#events-syntax" title="23.4.3 Event Syntax">Section 23.4.3, “Event Syntax”</a>, discusses the SQL statements
        for creating, altering, and dropping MySQL Events.
      </p></li><li class="listitem"><p>
        <a class="xref" href="stored-programs-views.html#events-metadata" title="23.4.4 Event Metadata">Section 23.4.4, “Event Metadata”</a>, shows how to obtain
        information about events and how this information is stored by
        the MySQL Server.
      </p></li><li class="listitem"><p>
        <a class="xref" href="stored-programs-views.html#events-privileges" title="23.4.6 The Event Scheduler and MySQL Privileges">Section 23.4.6, “The Event Scheduler and MySQL Privileges”</a>, discusses the privileges
        required to work with events and the ramifications that events
        have with regard to privileges when executing.
</p></li></ul>
</div>
<p>
    Stored routines require the <code class="literal">event</code> table in the
    <code class="literal">mysql</code> database. This table is created during the
    MySQL 5.7 installation procedure. If you are upgrading
    to MySQL 5.7 from an earlier version, be sure to update
    your grant tables to make sure that the <code class="literal">event</code>
    table exists. See <a class="xref" href="programs.html#mysql-upgrade" title="4.4.7 mysql_upgrade — Check and Upgrade MySQL Tables">Section 4.4.7, “<span class="command"><strong>mysql_upgrade</strong></span> — Check and Upgrade MySQL Tables”</a>.
</p>
<h3><a name="idm139684638003264"></a>Additional Resources</h3>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        You may find the <a class="ulink" href="http://forums.mysql.com/list.php?119" target="_top">MySQL
        Event Scheduler User Forum</a> of use when working with
        scheduled events.
      </p></li><li class="listitem"><p>
        There are some restrictions on the use of events; see
        <a class="xref" href="restrictions.html#stored-program-restrictions" title="C.1 Restrictions on Stored Programs">Section C.1, “Restrictions on Stored Programs”</a>.
      </p></li><li class="listitem"><p>
        Binary logging for events takes place as described in
        <a class="xref" href="stored-programs-views.html#stored-programs-logging" title="23.7 Binary Logging of Stored Programs">Section 23.7, “Binary Logging of Stored Programs”</a>.
</p></li></ul>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="events-overview"></a>23.4.1 Event Scheduler Overview</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm139684637996336"></a><p>
      MySQL Events are tasks that run according to a schedule.
      Therefore, we sometimes refer to them as
      <span class="emphasis"><em>scheduled</em></span> events. When you create an event,
      you are creating a named database object containing one or more
      SQL statements to be executed at one or more regular intervals,
      beginning and ending at a specific date and time. Conceptually,
      this is similar to the idea of the Unix <code class="literal">crontab</code>
      (also known as a <span class="quote">“<span class="quote">cron job</span>”</span>) or the Windows Task
      Scheduler.
    </p><p>
      Scheduled tasks of this type are also sometimes known as
      <span class="quote">“<span class="quote">temporal triggers</span>”</span>, implying that these are objects
      that are triggered by the passage of time. While this is
      essentially correct, we prefer to use the term
      <span class="emphasis"><em>events</em></span> to avoid confusion with triggers of
      the type discussed in <a class="xref" href="stored-programs-views.html#triggers" title="23.3 Using Triggers">Section 23.3, “Using Triggers”</a>. Events should
      more specifically not be confused with <span class="quote">“<span class="quote">temporary
      triggers</span>”</span>. Whereas a trigger is a database object whose
      statements are executed in response to a specific type of event
      that occurs on a given table, a (scheduled) event is an object
      whose statements are executed in response to the passage of a
      specified time interval.
    </p><p>
      While there is no provision in the SQL Standard for event
      scheduling, there are precedents in other database systems, and
      you may notice some similarities between these implementations and
      that found in the MySQL Server.
    </p><p>
      MySQL Events have the following major features and properties:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          In MySQL, an event is uniquely identified by its name and the
          schema to which it is assigned.
        </p></li><li class="listitem"><p>
          An event performs a specific action according to a schedule.
          This action consists of an SQL statement, which can be a
          compound statement in a
          <a class="link" href="sql-syntax.html#begin-end" title="13.6.1 BEGIN ... END Compound-Statement Syntax"><code class="literal">BEGIN ...
          END</code></a> block if desired (see
          <a class="xref" href="sql-syntax.html#sql-syntax-compound-statements" title="13.6 Compound-Statement Syntax">Section 13.6, “Compound-Statement Syntax”</a>). An event's
          timing can be either
          <span class="firstterm">one-time</span> or
          <span class="firstterm">recurrent</span>. A one-time
          event executes one time only. A recurrent event repeats its
          action at a regular interval, and the schedule for a recurring
          event can be assigned a specific start day and time, end day
          and time, both, or neither. (By default, a recurring event's
          schedule begins as soon as it is created, and continues
          indefinitely, until it is disabled or dropped.)
        </p><p>
          If a repeating event does not terminate within its scheduling
          interval, the result may be multiple instances of the event
          executing simultaneously. If this is undesirable, you should
          institute a mechanism to prevent simultaneous instances. For
          example, you could use the
          <a class="link" href="functions.html#function_get-lock"><code class="literal">GET_LOCK()</code></a> function, or row or
          table locking.
        </p></li><li class="listitem"><p>
          Users can create, modify, and drop scheduled events using SQL
          statements intended for these purposes. Syntactically invalid
          event creation and modification statements fail with an
          appropriate error message. <span class="emphasis"><em>A user may include
          statements in an event's action which require privileges that
          the user does not actually have</em></span>. The event creation
          or modification statement succeeds but the event's action
          fails. See <a class="xref" href="stored-programs-views.html#events-privileges" title="23.4.6 The Event Scheduler and MySQL Privileges">Section 23.4.6, “The Event Scheduler and MySQL Privileges”</a> for details.
        </p></li><li class="listitem"><p>
          Many of the properties of an event can be set or modified
          using SQL statements. These properties include the event's
          name, timing, persistence (that is, whether it is preserved
          following the expiration of its schedule), status (enabled or
          disabled), action to be performed, and the schema to which it
          is assigned. See <a class="xref" href="sql-syntax.html#alter-event" title="13.1.2 ALTER EVENT Syntax">Section 13.1.2, “ALTER EVENT Syntax”</a>.
        </p><p>
          The default definer of an event is the user who created the
          event, unless the event has been altered, in which case the
          definer is the user who issued the last
          <a class="link" href="sql-syntax.html#alter-event" title="13.1.2 ALTER EVENT Syntax"><code class="literal">ALTER EVENT</code></a> statement affecting
          that event. An event can be modified by any user having the
          <a class="link" href="security.html#priv_event"><code class="literal">EVENT</code></a> privilege on the database
          for which the event is defined. See
          <a class="xref" href="stored-programs-views.html#events-privileges" title="23.4.6 The Event Scheduler and MySQL Privileges">Section 23.4.6, “The Event Scheduler and MySQL Privileges”</a>.
        </p></li><li class="listitem"><p>
          An event's action statement may include most SQL statements
          permitted within stored routines. For restrictions, see
          <a class="xref" href="restrictions.html#stored-program-restrictions" title="C.1 Restrictions on Stored Programs">Section C.1, “Restrictions on Stored Programs”</a>.
</p></li></ul>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="events-configuration"></a>23.4.2 Event Scheduler Configuration</h3>

</div>

</div>

</div>
<p>
      Events are executed by a special <span class="firstterm">event
      scheduler thread</span>; when we refer to the Event Scheduler,
      we actually refer to this thread. When running, the event
      scheduler thread and its current state can be seen by users having
      the <a class="link" href="security.html#priv_process"><code class="literal">PROCESS</code></a> privilege in the output
      of <a class="link" href="sql-syntax.html#show-processlist" title="13.7.5.29 SHOW PROCESSLIST Syntax"><code class="literal">SHOW PROCESSLIST</code></a>, as shown in
      the discussion that follows.
    </p><a class="indexterm" name="idm139684637964912"></a><a class="indexterm" name="idm139684637963424"></a><p><a name="events-event-scheduler-option"></a>
      The global <a class="link" href="server-administration.html#sysvar_event_scheduler"><code class="literal">event_scheduler</code></a> system
      variable determines whether the Event Scheduler is enabled and
      running on the server. It has one of these 3 values, which affect
      event scheduling as described here:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          <code class="literal">OFF</code>: The Event Scheduler is stopped. The
          event scheduler thread does not run, is not shown in the
          output of <a class="link" href="sql-syntax.html#show-processlist" title="13.7.5.29 SHOW PROCESSLIST Syntax"><code class="literal">SHOW PROCESSLIST</code></a>, and
          no scheduled events are executed. <code class="literal">OFF</code> is
          the default value for
          <a class="link" href="server-administration.html#sysvar_event_scheduler"><code class="literal">event_scheduler</code></a>.
        </p><p>
          When the Event Scheduler is stopped
          (<a class="link" href="server-administration.html#sysvar_event_scheduler"><code class="literal">event_scheduler</code></a> is
          <code class="literal">OFF</code>), it can be started by setting the
          value of <a class="link" href="server-administration.html#sysvar_event_scheduler"><code class="literal">event_scheduler</code></a> to
          <code class="literal">ON</code>. (See next item.)
        </p></li><li class="listitem"><p>
          <code class="literal">ON</code>: The Event Scheduler is started; the
          event scheduler thread runs and executes all scheduled events.
        </p><a class="indexterm" name="idm139684637948832"></a><p>
          When the Event Scheduler is <code class="literal">ON</code>, the event
          scheduler thread is listed in the output of
          <a class="link" href="sql-syntax.html#show-processlist" title="13.7.5.29 SHOW PROCESSLIST Syntax"><code class="literal">SHOW PROCESSLIST</code></a> as a daemon
          process, and its state is represented as shown here:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SHOW PROCESSLIST\G</code></strong>
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 2
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 3
  State: Waiting for next activation
   Info: NULL
2 rows in set (0.00 sec)
</pre><p>
          Event scheduling can be stopped by setting the value of
          <a class="link" href="server-administration.html#sysvar_event_scheduler"><code class="literal">event_scheduler</code></a> to
          <code class="literal">OFF</code>.
        </p></li><li class="listitem"><p>
          <code class="literal">DISABLED</code>: This value renders the Event
          Scheduler nonoperational. When the Event Scheduler is
          <code class="literal">DISABLED</code>, the event scheduler thread does
          not run (and so does not appear in the output of
          <a class="link" href="sql-syntax.html#show-processlist" title="13.7.5.29 SHOW PROCESSLIST Syntax"><code class="literal">SHOW PROCESSLIST</code></a>). In addition,
          the Event Scheduler state cannot be changed at runtime.
</p></li></ul>
</div>
<p>
      If the Event Scheduler status has not been set to
      <code class="literal">DISABLED</code>,
      <a class="link" href="server-administration.html#sysvar_event_scheduler"><code class="literal">event_scheduler</code></a> can be toggled
      between <code class="literal">ON</code> and <code class="literal">OFF</code> (using
      <a class="link" href="sql-syntax.html#set-variable" title="13.7.4.1 SET Syntax for Variable Assignment"><code class="literal">SET</code></a>). It
      is also possible to use <code class="literal">0</code> for
      <code class="literal">OFF</code>, and <code class="literal">1</code> for
      <code class="literal">ON</code> when setting this variable. Thus, any of the
      following 4 statements can be used in the <a class="link" href="programs.html#mysql" title="4.5.1 mysql — The MySQL Command-Line Tool"><span class="command"><strong>mysql</strong></span></a>
      client to turn on the Event Scheduler:
    </p><pre data-lang="sql" class="programlisting">
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;
</pre><p>
      Similarly, any of these 4 statements can be used to turn off the
      Event Scheduler:
    </p><pre data-lang="sql" class="programlisting">
SET GLOBAL event_scheduler = OFF;
SET @@global.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@global.event_scheduler = 0;
</pre><p>
      Although <code class="literal">ON</code> and <code class="literal">OFF</code> have
      numeric equivalents, the value displayed for
      <a class="link" href="server-administration.html#sysvar_event_scheduler"><code class="literal">event_scheduler</code></a> by
      <a class="link" href="sql-syntax.html#select" title="13.2.9 SELECT Syntax"><code class="literal">SELECT</code></a> or <a class="link" href="sql-syntax.html#show-variables" title="13.7.5.39 SHOW VARIABLES Syntax"><code class="literal">SHOW
      VARIABLES</code></a> is always one of <code class="literal">OFF</code>,
      <code class="literal">ON</code>, or <code class="literal">DISABLED</code>.
      <span class="emphasis"><em><code class="literal">DISABLED</code> has no numeric
      equivalent</em></span>. For this reason, <code class="literal">ON</code> and
      <code class="literal">OFF</code> are usually preferred over
      <code class="literal">1</code> and <code class="literal">0</code> when setting this
      variable.
    </p><p>
      Note that attempting to set
      <a class="link" href="server-administration.html#sysvar_event_scheduler"><code class="literal">event_scheduler</code></a> without
      specifying it as a global variable causes an error:
    </p><pre data-lang="sql" class="programlisting">
mysql&lt; <strong class="userinput"><code>SET @@event_scheduler = OFF;</code></strong>
<span class="errortext">ERROR 1229 (HY000): Variable 'event_scheduler' is a GLOBAL
variable and should be set with SET GLOBAL</span>
</pre>
<div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Important
</div>
<p>
        It is possible to set the Event Scheduler to
        <code class="literal">DISABLED</code> only at server startup. If
        <a class="link" href="server-administration.html#sysvar_event_scheduler"><code class="literal">event_scheduler</code></a> is
        <code class="literal">ON</code> or <code class="literal">OFF</code>, you cannot set
        it to <code class="literal">DISABLED</code> at runtime. Also, if the Event
        Scheduler is set to <code class="literal">DISABLED</code> at startup, you
        cannot change the value of
        <a class="link" href="server-administration.html#sysvar_event_scheduler"><code class="literal">event_scheduler</code></a> at runtime.
</p>
</div>
<p>
      To disable the event scheduler, use one of the following two
      methods:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          As a command-line option when starting the server:
        </p><pre data-lang="terminal" class="programlisting">
--event-scheduler=DISABLED
</pre></li><li class="listitem"><p>
          In the server configuration file (<code class="filename">my.cnf</code>,
          or <code class="filename">my.ini</code> on Windows systems), include
          the line where it will be read by the server (for example, in
          a <code class="literal">[mysqld]</code> section):
        </p><pre data-lang="ini" class="programlisting">
event_scheduler=DISABLED
</pre></li></ul>
</div>
<p>
      To enable the Event Scheduler, restart the server without the
      <a class="link" href="server-administration.html#option_mysqld_event-scheduler"><code class="option">--event-scheduler=DISABLED</code></a>
      command-line option, or after removing or commenting out the line
      containing <a class="link" href="server-administration.html#option_mysqld_event-scheduler"><code class="option">event-scheduler=DISABLED</code></a>
      in the server configuration file, as appropriate. Alternatively,
      you can use <code class="literal">ON</code> (or <code class="literal">1</code>) or
      <code class="literal">OFF</code> (or <code class="literal">0</code>) in place of the
      <code class="literal">DISABLED</code> value when starting the server.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Note
</div>
<p>
        You can issue event-manipulation statements when
        <a class="link" href="server-administration.html#sysvar_event_scheduler"><code class="literal">event_scheduler</code></a> is set to
        <code class="literal">DISABLED</code>. No warnings or errors are generated
        in such cases (provided that the statements are themselves
        valid). However, scheduled events cannot execute until this
        variable is set to <code class="literal">ON</code> (or
        <code class="literal">1</code>). Once this has been done, the event
        scheduler thread executes all events whose scheduling conditions
        are satisfied.
</p>
</div>
<p>
      Starting the MySQL server with the
      <a class="link" href="server-administration.html#option_mysqld_skip-grant-tables"><code class="option">--skip-grant-tables</code></a> option causes
      <a class="link" href="server-administration.html#sysvar_event_scheduler"><code class="literal">event_scheduler</code></a> to be set to
      <code class="literal">DISABLED</code>, overriding any other value set either
      on the command line or in the <code class="filename">my.cnf</code> or
      <code class="filename">my.ini</code> file (Bug #26807).
    </p><p>
      For SQL statements used to create, alter, and drop events, see
      <a class="xref" href="stored-programs-views.html#events-syntax" title="23.4.3 Event Syntax">Section 23.4.3, “Event Syntax”</a>.
    </p><p>
      MySQL provides an <a class="link" href="information-schema.html#events-table" title="24.8 The INFORMATION_SCHEMA EVENTS Table"><code class="literal">EVENTS</code></a> table in the
      <code class="literal">INFORMATION_SCHEMA</code> database. This table can be
      queried to obtain information about scheduled events which have
      been defined on the server. See <a class="xref" href="stored-programs-views.html#events-metadata" title="23.4.4 Event Metadata">Section 23.4.4, “Event Metadata”</a>,
      and <a class="xref" href="information-schema.html#events-table" title="24.8 The INFORMATION_SCHEMA EVENTS Table">Section 24.8, “The INFORMATION_SCHEMA EVENTS Table”</a>, for more information.
    </p><p>
      For information regarding event scheduling and the MySQL privilege
      system, see <a class="xref" href="stored-programs-views.html#events-privileges" title="23.4.6 The Event Scheduler and MySQL Privileges">Section 23.4.6, “The Event Scheduler and MySQL Privileges”</a>.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="events-syntax"></a>23.4.3 Event Syntax</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm139684637872768"></a><p>
      MySQL provides several SQL statements for working with scheduled
      events:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          New events are defined using the <a class="link" href="sql-syntax.html#create-event" title="13.1.12 CREATE EVENT Syntax"><code class="literal">CREATE
          EVENT</code></a> statement. See <a class="xref" href="sql-syntax.html#create-event" title="13.1.12 CREATE EVENT Syntax">Section 13.1.12, “CREATE EVENT Syntax”</a>.
        </p></li><li class="listitem"><p>
          The definition of an existing event can be changed by means of
          the <a class="link" href="sql-syntax.html#alter-event" title="13.1.2 ALTER EVENT Syntax"><code class="literal">ALTER EVENT</code></a> statement. See
          <a class="xref" href="sql-syntax.html#alter-event" title="13.1.2 ALTER EVENT Syntax">Section 13.1.2, “ALTER EVENT Syntax”</a>.
        </p></li><li class="listitem"><p>
          When a scheduled event is no longer wanted or needed, it can
          be deleted from the server by its definer using the
          <a class="link" href="sql-syntax.html#drop-event" title="13.1.23 DROP EVENT Syntax"><code class="literal">DROP EVENT</code></a> statement. See
          <a class="xref" href="sql-syntax.html#drop-event" title="13.1.23 DROP EVENT Syntax">Section 13.1.23, “DROP EVENT Syntax”</a>. Whether an event persists past
          the end of its schedule also depends on its <code class="literal">ON
          COMPLETION</code> clause, if it has one. See
          <a class="xref" href="sql-syntax.html#create-event" title="13.1.12 CREATE EVENT Syntax">Section 13.1.12, “CREATE EVENT Syntax”</a>.
        </p><p>
          An event can be dropped by any user having the
          <a class="link" href="security.html#priv_event"><code class="literal">EVENT</code></a> privilege for the
          database on which the event is defined. See
          <a class="xref" href="stored-programs-views.html#events-privileges" title="23.4.6 The Event Scheduler and MySQL Privileges">Section 23.4.6, “The Event Scheduler and MySQL Privileges”</a>.
</p></li></ul>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="events-metadata"></a>23.4.4 Event Metadata</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm139684637856880"></a><a class="indexterm" name="idm139684637855392"></a><p>
      Metadata about events can be obtained as follows:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Query the <code class="literal">event</code> table of the
          <code class="literal">mysql</code> database.
        </p></li><li class="listitem"><p>
          Query the <a class="link" href="information-schema.html#events-table" title="24.8 The INFORMATION_SCHEMA EVENTS Table"><code class="literal">EVENTS</code></a> table of the
          <code class="literal">INFORMATION_SCHEMA</code> database. See
          <a class="xref" href="information-schema.html#events-table" title="24.8 The INFORMATION_SCHEMA EVENTS Table">Section 24.8, “The INFORMATION_SCHEMA EVENTS Table”</a>.
        </p></li><li class="listitem"><p>
          Use the <a class="link" href="sql-syntax.html#show-create-event" title="13.7.5.7 SHOW CREATE EVENT Syntax"><code class="literal">SHOW CREATE EVENT</code></a>
          statement. See <a class="xref" href="sql-syntax.html#show-create-event" title="13.7.5.7 SHOW CREATE EVENT Syntax">Section 13.7.5.7, “SHOW CREATE EVENT Syntax”</a>.
        </p></li><li class="listitem"><p>
          Use the <a class="link" href="sql-syntax.html#show-events" title="13.7.5.18 SHOW EVENTS Syntax"><code class="literal">SHOW EVENTS</code></a> statement.
          See <a class="xref" href="sql-syntax.html#show-events" title="13.7.5.18 SHOW EVENTS Syntax">Section 13.7.5.18, “SHOW EVENTS Syntax”</a>.
</p></li></ul>
</div>
<p>
      <span class="bold"><strong>Event Scheduler Time
      Representation</strong></span>
    </p><a class="indexterm" name="idm139684637840800"></a><a class="indexterm" name="idm139684637839312"></a><p>
      Each session in MySQL has a session time zone (STZ). This is the
      session <a class="link" href="server-administration.html#sysvar_time_zone"><code class="literal">time_zone</code></a> value that is
      initialized from the server's global
      <a class="link" href="server-administration.html#sysvar_time_zone"><code class="literal">time_zone</code></a> value when the session
      begins but may be changed during the session.
    </p><p>
      The session time zone that is current when a
      <a class="link" href="sql-syntax.html#create-event" title="13.1.12 CREATE EVENT Syntax"><code class="literal">CREATE EVENT</code></a> or
      <a class="link" href="sql-syntax.html#alter-event" title="13.1.2 ALTER EVENT Syntax"><code class="literal">ALTER EVENT</code></a> statement executes is
      used to interpret times specified in the event definition. This
      becomes the event time zone (ETZ); that is, the time zone that is
      used for event scheduling and is in effect within the event as it
      executes.
    </p><p>
      For representation of event information in the
      <code class="literal">mysql.event</code> table, the
      <code class="literal">execute_at</code>, <code class="literal">starts</code>, and
      <code class="literal">ends</code> times are converted to UTC and stored
      along with the event time zone. This enables event execution to
      proceed as defined regardless of any subsequent changes to the
      server time zone or daylight saving time effects. The
      <code class="literal">last_executed</code> time is also stored in UTC.
    </p><p>
      If you select information from <code class="literal">mysql.event</code>, the
      times just mentioned are retrieved as UTC values. These times can
      also be obtained by selecting from the
      <a class="link" href="information-schema.html#events-table" title="24.8 The INFORMATION_SCHEMA EVENTS Table"><code class="literal">INFORMATION_SCHEMA.EVENTS</code></a> table or
      from <a class="link" href="sql-syntax.html#show-events" title="13.7.5.18 SHOW EVENTS Syntax"><code class="literal">SHOW EVENTS</code></a>, but they are
      reported as ETZ values. Other times available from these sources
      indicate when an event was created or last altered; these are
      displayed as STZ values. The following table summarizes
      representation of event times.
</p>
<div class="informaltable">
<table summary="Summary of event time representation (as UTC, EZT, or STZ values) from mysql.event, INFORMATION_SCHEMA.EVENTS, and SHOW EVENTS."><col width="25%"><col width="25%"><col width="25%"><col width="25%"><thead><tr>
          <th scope="col">Value</th>
          <th scope="col"><code class="literal">mysql.event</code></th>
          <th scope="col"><a class="link" href="information-schema.html#events-table" title="24.8 The INFORMATION_SCHEMA EVENTS Table"><code class="literal">INFORMATION_SCHEMA.EVENTS</code></a></th>
          <th scope="col"><a class="link" href="sql-syntax.html#show-events" title="13.7.5.18 SHOW EVENTS Syntax"><code class="literal">SHOW EVENTS</code></a></th>
        </tr></thead><tbody><tr>
          <td scope="row">Execute at</td>
          <td>UTC</td>
          <td>ETZ</td>
          <td>ETZ</td>
        </tr><tr>
          <td scope="row">Starts</td>
          <td>UTC</td>
          <td>ETZ</td>
          <td>ETZ</td>
        </tr><tr>
          <td scope="row">Ends</td>
          <td>UTC</td>
          <td>ETZ</td>
          <td>ETZ</td>
        </tr><tr>
          <td scope="row">Last executed</td>
          <td>UTC</td>
          <td>ETZ</td>
          <td>n/a</td>
        </tr><tr>
          <td scope="row">Created</td>
          <td>STZ</td>
          <td>STZ</td>
          <td>n/a</td>
        </tr><tr>
          <td scope="row">Last altered</td>
          <td>STZ</td>
          <td>STZ</td>
          <td>n/a</td>
</tr></tbody></table>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="events-status-info"></a>23.4.5 Event Scheduler Status</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm139684637785648"></a><a class="indexterm" name="idm139684637784144"></a><a class="indexterm" name="idm139684637783072"></a><p>
      The Event Scheduler writes information about event execution that
      terminates with an error or warning to the MySQL Server's error
      log. See <a class="xref" href="stored-programs-views.html#events-privileges" title="23.4.6 The Event Scheduler and MySQL Privileges">Section 23.4.6, “The Event Scheduler and MySQL Privileges”</a> for an example.
    </p><p>
      To obtain information about the state of the Event Scheduler for
      debugging and troubleshooting purposes, run <a class="link" href="programs.html#mysqladmin" title="4.5.2 mysqladmin — Client for Administering a MySQL Server"><span class="command"><strong>mysqladmin
      debug</strong></span></a> (see <a class="xref" href="programs.html#mysqladmin" title="4.5.2 mysqladmin — Client for Administering a MySQL Server">Section 4.5.2, “<span class="command"><strong>mysqladmin</strong></span> — Client for Administering a MySQL Server”</a>); after running
      this command, the server's error log contains output relating to
      the Event Scheduler, similar to what is shown here:
    </p><pre data-lang="none" class="programlisting">
Events status:
LLA = Last Locked At  LUA = Last Unlocked At
WOC = Waiting On Condition  DL = Data Locked

Event scheduler status:
State      : INITIALIZED
Thread id  : 0
LLA        : init_scheduler:313
LUA        : init_scheduler:318
WOC        : NO
Workers    : 0
Executed   : 0
Data locked: NO

Event queue status:
Element count   : 1
Data locked     : NO
Attempting lock : NO
LLA             : init_queue:148
LUA             : init_queue:168
WOC             : NO
Next activation : 0000-00-00 00:00:00
</pre><p>
      In statements that occur as part of events executed by the Event
      Scheduler, diagnostics messages (not only errors, but also
      warnings) are written to the error log, and, on Windows, to the
      application event log. For frequently executed events, it is
      possible for this to result in many logged messages. For example,
      for <code class="literal">SELECT ... INTO
      <em class="replaceable"><code>var_list</code></em></code> statements, if the
      query returns no rows, a warning with error code 1329 occurs
      (<code class="literal">No data</code>), and the variable values remain
      unchanged. If the query returns multiple rows, error 1172 occurs
      (<code class="literal">Result consisted of more than one row</code>). For
      either condition, you can avoid having the warnings be logged by
      declaring a condition handler; see
      <a class="xref" href="sql-syntax.html#declare-handler" title="13.6.7.2 DECLARE ... HANDLER Syntax">Section 13.6.7.2, “DECLARE ... HANDLER Syntax”</a>. For statements that may
      retrieve multiple rows, another strategy is to use <code class="literal">LIMIT
      1</code> to limit the result set to a single row.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="events-privileges"></a>23.4.6 The Event Scheduler and MySQL Privileges</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm139684637770048"></a><p>
      To enable or disable the execution of scheduled events, it is
      necessary to set the value of the global
      <a class="link" href="server-administration.html#sysvar_event_scheduler"><code class="literal">event_scheduler</code></a> system variable.
      This requires the <a class="link" href="security.html#priv_super"><code class="literal">SUPER</code></a> privilege.
    </p><p>
      The <a class="link" href="security.html#priv_event"><code class="literal">EVENT</code></a> privilege governs the
      creation, modification, and deletion of events. This privilege can
      be bestowed using <a class="link" href="sql-syntax.html#grant" title="13.7.1.4 GRANT Syntax"><code class="literal">GRANT</code></a>. For
      example, this <a class="link" href="sql-syntax.html#grant" title="13.7.1.4 GRANT Syntax"><code class="literal">GRANT</code></a> statement
      confers the <a class="link" href="security.html#priv_event"><code class="literal">EVENT</code></a> privilege for the
      schema named <code class="literal">myschema</code> on the user
      <code class="literal">jon@ghidora</code>:
    </p><pre data-lang="sql" class="programlisting">
GRANT EVENT ON myschema.* TO jon@ghidora;
</pre><p>
      (We assume that this user account already exists, and that we wish
      for it to remain unchanged otherwise.)
    </p><p>
      To grant this same user the <a class="link" href="security.html#priv_event"><code class="literal">EVENT</code></a>
      privilege on all schemas, use the following statement:
    </p><pre data-lang="sql" class="programlisting">
GRANT EVENT ON *.* TO jon@ghidora;
</pre><p>
      The <a class="link" href="security.html#priv_event"><code class="literal">EVENT</code></a> privilege has global or
      schema-level scope. Therefore, trying to grant it on a single
      table results in an error as shown:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>GRANT EVENT ON myschema.mytable TO jon@ghidora;</code></strong>
<span class="errortext">ERROR 1144 (42000): Illegal GRANT/REVOKE command; please
consult the manual to see which privileges can be used</span>
</pre><p>
      It is important to understand that an event is executed with the
      privileges of its definer, and that it cannot perform any actions
      for which its definer does not have the requisite privileges. For
      example, suppose that <code class="literal">jon@ghidora</code> has the
      <a class="link" href="security.html#priv_event"><code class="literal">EVENT</code></a> privilege for
      <code class="literal">myschema</code>. Suppose also that this user has the
      <a class="link" href="security.html#priv_select"><code class="literal">SELECT</code></a> privilege for
      <code class="literal">myschema</code>, but no other privileges for this
      schema. It is possible for <code class="literal">jon@ghidora</code> to
      create a new event such as this one:
    </p><pre data-lang="sql" class="programlisting">
CREATE EVENT e_store_ts
    ON SCHEDULE
      EVERY 10 SECOND
    DO
      INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());
</pre><p>
      The user waits for a minute or so, and then performs a
      <code class="literal">SELECT * FROM mytable;</code> query, expecting to see
      several new rows in the table. Instead, the table is empty. Since
      the user does not have the <a class="link" href="security.html#priv_insert"><code class="literal">INSERT</code></a>
      privilege for the table in question, the event has no effect.
    </p><p>
      If you inspect the MySQL error log
      (<code class="filename"><em class="replaceable"><code>hostname</code></em>.err</code>),
      you can see that the event is executing, but the action it is
      attempting to perform fails:
    </p><pre data-lang="none" class="programlisting">
2013-09-24T12:41:31.261992Z 25 [ERROR] Event Scheduler:
[jon@ghidora][cookbook.e_store_ts] INSERT command denied to user
'jon'@'ghidora' for table 'mytable'
2013-09-24T12:41:31.262022Z 25 [Note] Event Scheduler:
[jon@ghidora].[myschema.e_store_ts] event execution failed.
2013-09-24T12:41:41.271796Z 26 [ERROR] Event Scheduler:
[jon@ghidora][cookbook.e_store_ts] INSERT command denied to user
'jon'@'ghidora' for table 'mytable'
2013-09-24T12:41:41.272761Z 26 [Note] Event Scheduler:
[jon@ghidora].[myschema.e_store_ts] event execution failed.
</pre><p>
      Since this user very likely does not have access to the error log,
      it is possible to verify whether the event's action statement is
      valid by executing it directly:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());</code></strong>
<span class="errortext">ERROR 1142 (42000): INSERT command denied to user
'jon'@'ghidora' for table 'mytable'</span>
</pre><a class="indexterm" name="idm139684637735056"></a><p>
      Inspection of the
      <a class="link" href="information-schema.html#events-table" title="24.8 The INFORMATION_SCHEMA EVENTS Table"><code class="literal">INFORMATION_SCHEMA.EVENTS</code></a> table shows
      that <code class="literal">e_store_ts</code> exists and is enabled, but its
      <code class="literal">LAST_EXECUTED</code> column is
      <code class="literal">NULL</code>:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT * FROM INFORMATION_SCHEMA.EVENTS</code></strong>
     &gt;     <strong class="userinput"><code>WHERE EVENT_NAME='e_store_ts'</code></strong>
     &gt;     <strong class="userinput"><code>AND EVENT_SCHEMA='myschema'\G</code></strong>
*************************** 1. row ***************************
   EVENT_CATALOG: NULL
    EVENT_SCHEMA: myschema
      EVENT_NAME: e_store_ts
         DEFINER: jon@ghidora
      EVENT_BODY: SQL
EVENT_DEFINITION: INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP())
      EVENT_TYPE: RECURRING
      EXECUTE_AT: NULL
  INTERVAL_VALUE: 5
  INTERVAL_FIELD: SECOND
        SQL_MODE: NULL
          STARTS: 0000-00-00 00:00:00
            ENDS: 0000-00-00 00:00:00
          STATUS: ENABLED
   ON_COMPLETION: NOT PRESERVE
         CREATED: 2006-02-09 22:36:06
    LAST_ALTERED: 2006-02-09 22:36:06
   LAST_EXECUTED: NULL
   EVENT_COMMENT:
1 row in set (0.00 sec)
</pre><p>
      To rescind the <a class="link" href="security.html#priv_event"><code class="literal">EVENT</code></a> privilege, use
      the <a class="link" href="sql-syntax.html#revoke" title="13.7.1.6 REVOKE Syntax"><code class="literal">REVOKE</code></a> statement. In this
      example, the <a class="link" href="security.html#priv_event"><code class="literal">EVENT</code></a> privilege on the
      schema <code class="literal">myschema</code> is removed from the
      <code class="literal">jon@ghidora</code> user account:
    </p><pre data-lang="sql" class="programlisting">
REVOKE EVENT ON myschema.* FROM jon@ghidora;
</pre>
<div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">

<div class="admon-title">
Important
</div>
<p>
        Revoking the <a class="link" href="security.html#priv_event"><code class="literal">EVENT</code></a> privilege from
        a user does not delete or disable any events that may have been
        created by that user.
      </p><p>
        An event is not migrated or dropped as a result of renaming or
        dropping the user who created it.
</p>
</div>
<p>
      Suppose that the user <code class="literal">jon@ghidora</code> has been
      granted the <a class="link" href="security.html#priv_event"><code class="literal">EVENT</code></a> and
      <a class="link" href="security.html#priv_insert"><code class="literal">INSERT</code></a> privileges on the
      <code class="literal">myschema</code> schema. This user then creates the
      following event:
    </p><pre data-lang="sql" class="programlisting">
CREATE EVENT e_insert
    ON SCHEDULE
      EVERY 7 SECOND
    DO
      INSERT INTO myschema.mytable;
</pre><p>
      After this event has been created, <code class="literal">root</code> revokes
      the <a class="link" href="security.html#priv_event"><code class="literal">EVENT</code></a> privilege for
      <code class="literal">jon@ghidora</code>. However,
      <code class="literal">e_insert</code> continues to execute, inserting a new
      row into <code class="literal">mytable</code> each seven seconds. The same
      would be true if <code class="literal">root</code> had issued either of
      these statements:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          <code class="literal">DROP USER jon@ghidora;</code>
        </p></li><li class="listitem"><p>
          <code class="literal">RENAME USER jon@ghidora TO
          someotherguy@ghidora;</code>
</p></li></ul>
</div>
<p>
      You can verify that this is true by examining the
      <code class="literal">mysql.event</code> table (discussed later in this
      section) or the
      <a class="link" href="information-schema.html#events-table" title="24.8 The INFORMATION_SCHEMA EVENTS Table"><code class="literal">INFORMATION_SCHEMA.EVENTS</code></a> table (see
      <a class="xref" href="information-schema.html#events-table" title="24.8 The INFORMATION_SCHEMA EVENTS Table">Section 24.8, “The INFORMATION_SCHEMA EVENTS Table”</a>) before and after issuing a
      <a class="link" href="sql-syntax.html#drop-user" title="13.7.1.3 DROP USER Syntax"><code class="literal">DROP USER</code></a> or
      <a class="link" href="sql-syntax.html#rename-user" title="13.7.1.5 RENAME USER Syntax"><code class="literal">RENAME USER</code></a> statement.
    </p><a class="indexterm" name="idm139684637696880"></a><p>
      Event definitions are stored in the <code class="literal">mysql.event</code>
      table. To drop an event created by another user account, the MySQL
      <code class="literal">root</code> user (or another user with the necessary
      privileges) can delete rows from this table. For example, to
      remove the event <code class="literal">e_insert</code> shown previously,
      <code class="literal">root</code> can use the following statement:
    </p><pre data-lang="sql" class="programlisting">
DELETE FROM mysql.event
    WHERE db = 'myschema'
      AND definer = 'jon@ghidora'
      AND name = 'e_insert';
</pre><p>
      It is very important to match the event name, database schema
      name, and user account when deleting rows from the
      <code class="literal">mysql.event</code> table. This is because the same
      user can create different events of the same name in different
      schemas.
    </p><p>
      Users' <a class="link" href="security.html#priv_event"><code class="literal">EVENT</code></a> privileges are stored
      in the <code class="literal">Event_priv</code> columns of the
      <code class="literal">mysql.user</code> and <code class="literal">mysql.db</code>
      tables. In both cases, this column holds one of the values
      '<code class="literal">Y</code>' or '<code class="literal">N</code>'.
      '<code class="literal">N</code>' is the default.
      <code class="literal">mysql.user.Event_priv</code> is set to
      '<code class="literal">Y</code>' for a given user only if that user has the
      global <a class="link" href="security.html#priv_event"><code class="literal">EVENT</code></a> privilege (that is, if
      the privilege was bestowed using <code class="literal">GRANT EVENT ON
      *.*</code>). For a schema-level
      <a class="link" href="security.html#priv_event"><code class="literal">EVENT</code></a> privilege,
      <a class="link" href="sql-syntax.html#grant" title="13.7.1.4 GRANT Syntax"><code class="literal">GRANT</code></a> creates a row in
      <code class="literal">mysql.db</code> and sets that row's
      <code class="literal">Db</code> column to the name of the schema, the
      <code class="literal">User</code> column to the name of the user, and the
      <code class="literal">Event_priv</code> column to '<code class="literal">Y</code>'.
      There should never be any need to manipulate these tables
      directly, since the <a class="link" href="sql-syntax.html#grant" title="13.7.1.4 GRANT Syntax"><code class="literal">GRANT
      EVENT</code></a> and <code class="literal">REVOKE EVENT</code> statements
      perform the required operations on them.
    </p><a class="indexterm" name="idm139684637672256"></a><p>
      Five status variables provide counts of event-related operations
      (but <span class="emphasis"><em>not</em></span> of statements executed by events;
      see <a class="xref" href="restrictions.html#stored-program-restrictions" title="C.1 Restrictions on Stored Programs">Section C.1, “Restrictions on Stored Programs”</a>). These are:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          <code class="literal">Com_create_event</code>: The number of
          <a class="link" href="sql-syntax.html#create-event" title="13.1.12 CREATE EVENT Syntax"><code class="literal">CREATE EVENT</code></a> statements
          executed since the last server restart.
        </p></li><li class="listitem"><p>
          <code class="literal">Com_alter_event</code>: The number of
          <a class="link" href="sql-syntax.html#alter-event" title="13.1.2 ALTER EVENT Syntax"><code class="literal">ALTER EVENT</code></a> statements executed
          since the last server restart.
        </p></li><li class="listitem"><p>
          <code class="literal">Com_drop_event</code>: The number of
          <a class="link" href="sql-syntax.html#drop-event" title="13.1.23 DROP EVENT Syntax"><code class="literal">DROP EVENT</code></a> statements executed
          since the last server restart.
        </p></li><li class="listitem"><p>
          <code class="literal">Com_show_create_event</code>: The number of
          <a class="link" href="sql-syntax.html#show-create-event" title="13.7.5.7 SHOW CREATE EVENT Syntax"><code class="literal">SHOW CREATE EVENT</code></a> statements
          executed since the last server restart.
        </p></li><li class="listitem"><p>
          <code class="literal">Com_show_events</code>: The number of
          <a class="link" href="sql-syntax.html#show-events" title="13.7.5.18 SHOW EVENTS Syntax"><code class="literal">SHOW EVENTS</code></a> statements executed
          since the last server restart.
</p></li></ul>
</div>
<p>
      You can view current values for all of these at one time by
      running the statement <code class="literal">SHOW STATUS LIKE
      '%event%';</code>.
</p>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="views"></a>23.5 Using Views</h2>

</div>

</div>

</div>
<div class="toc">
<dl class="toc"><dt><span class="section"><a href="stored-programs-views.html#view-syntax">23.5.1 View Syntax</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#view-algorithms">23.5.2 View Processing Algorithms</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#view-updatability">23.5.3 Updatable and Insertable Views</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#view-check-option">23.5.4 The View WITH CHECK OPTION Clause</a></span></dt><dt><span class="section"><a href="stored-programs-views.html#view-metadata">23.5.5 View Metadata</a></span></dt></dl>
</div>
<a class="indexterm" name="idm139684637652192"></a><p>
    MySQL supports views, including updatable views. Views are stored
    queries that when invoked produce a result set. A view acts as a
    virtual table.
  </p><p>
    The following discussion describes the syntax for creating and
    dropping views, and shows some examples of how to use them.
</p>
<h3><a name="idm139684637650032"></a>Additional Resources</h3>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
        You may find the <a class="ulink" href="http://forums.mysql.com/list.php?100" target="_top">Views
        User Forum</a> of use when working with views.
      </p></li><li class="listitem"><p>
        For answers to some commonly asked questions regarding views in
        MySQL, see <a class="xref" href="faqs.html#faqs-views" title="A.6 MySQL 5.7 FAQ: Views">Section A.6, “MySQL 5.7 FAQ: Views”</a>.
      </p></li><li class="listitem"><p>
        There are some restrictions on the use of views; see
        <a class="xref" href="restrictions.html#view-restrictions" title="C.5 Restrictions on Views">Section C.5, “Restrictions on Views”</a>.
</p></li></ul>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="view-syntax"></a>23.5.1 View Syntax</h3>

</div>

</div>

</div>
<p>
      The <a class="link" href="sql-syntax.html#create-view" title="13.1.21 CREATE VIEW Syntax"><code class="literal">CREATE VIEW</code></a> statement creates a
      new view (see <a class="xref" href="sql-syntax.html#create-view" title="13.1.21 CREATE VIEW Syntax">Section 13.1.21, “CREATE VIEW Syntax”</a>). To alter the
      definition of a view or drop a view, use
      <a class="link" href="sql-syntax.html#alter-view" title="13.1.10 ALTER VIEW Syntax"><code class="literal">ALTER VIEW</code></a> (see
      <a class="xref" href="sql-syntax.html#alter-view" title="13.1.10 ALTER VIEW Syntax">Section 13.1.10, “ALTER VIEW Syntax”</a>), or <a class="link" href="sql-syntax.html#drop-view" title="13.1.32 DROP VIEW Syntax"><code class="literal">DROP
      VIEW</code></a> (see <a class="xref" href="sql-syntax.html#drop-view" title="13.1.32 DROP VIEW Syntax">Section 13.1.32, “DROP VIEW Syntax”</a>).
    </p><p>
      A view can be created from many kinds of
      <a class="link" href="sql-syntax.html#select" title="13.2.9 SELECT Syntax"><code class="literal">SELECT</code></a> statements. It can refer to
      base tables or other views. It can use joins,
      <a class="link" href="sql-syntax.html#union" title="13.2.9.3 UNION Syntax"><code class="literal">UNION</code></a>, and subqueries. The
      <a class="link" href="sql-syntax.html#select" title="13.2.9 SELECT Syntax"><code class="literal">SELECT</code></a> need not even refer to any
      tables. The following example defines a view that selects two
      columns from another table, as well as an expression calculated
      from those columns:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>CREATE TABLE t (qty INT, price INT);</code></strong>
mysql&gt; <strong class="userinput"><code>INSERT INTO t VALUES(3, 50), (5, 60);</code></strong>
mysql&gt; <strong class="userinput"><code>CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;</code></strong>
mysql&gt; <strong class="userinput"><code>SELECT * FROM v;</code></strong>
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
|    5 |    60 |   300 |
+------+-------+-------+
mysql&gt; <strong class="userinput"><code>SELECT * FROM v WHERE qty = 5;</code></strong>
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    5 |    60 |   300 |
+------+-------+-------+
</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="view-algorithms"></a>23.5.2 View Processing Algorithms</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm139684637626160"></a><p>
      The optional <code class="literal">ALGORITHM</code> clause for
      <a class="link" href="sql-syntax.html#create-view" title="13.1.21 CREATE VIEW Syntax"><code class="literal">CREATE VIEW</code></a> or
      <a class="link" href="sql-syntax.html#alter-view" title="13.1.10 ALTER VIEW Syntax"><code class="literal">ALTER VIEW</code></a> is a MySQL extension to
      standard SQL. It affects how MySQL processes the view.
      <code class="literal">ALGORITHM</code> takes three values:
      <code class="literal">MERGE</code>, <code class="literal">TEMPTABLE</code>, or
      <code class="literal">UNDEFINED</code>.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          For <code class="literal">MERGE</code>, the text of a statement that
          refers to the view and the view definition are merged such
          that parts of the view definition replace corresponding parts
          of the statement.
        </p></li><li class="listitem"><p>
          For <code class="literal">TEMPTABLE</code>, the results from the view
          are retrieved into a temporary table, which then is used to
          execute the statement.
        </p></li><li class="listitem"><p>
          For <code class="literal">UNDEFINED</code>, MySQL chooses which
          algorithm to use. It prefers <code class="literal">MERGE</code> over
          <code class="literal">TEMPTABLE</code> if possible, because
          <code class="literal">MERGE</code> is usually more efficient and because
          a view cannot be updatable if a temporary table is used.
        </p></li><li class="listitem"><p>
          If no <code class="literal">ALGORITHM</code> clause is present,
          <code class="literal">UNDEFINED</code> is the default algorithm prior to
          MySQL 5.7.6. As of 5.7.6, the default algorithm is determined
          by the value of the <code class="literal">derived_merge</code> flag of
          the <a class="link" href="server-administration.html#sysvar_optimizer_switch"><code class="literal">optimizer_switch</code></a> system
          variable. For additional discussion, see
          <a class="xref" href="optimization.html#derived-table-optimization" title="8.2.2.3 Optimizing Derived Tables and View References">Section 8.2.2.3, “Optimizing Derived Tables and View References”</a>.
</p></li></ul>
</div>
<p>
      A reason to specify <code class="literal">TEMPTABLE</code> explicitly is
      that locks can be released on underlying tables after the
      temporary table has been created and before it is used to finish
      processing the statement. This might result in quicker lock
      release than the <code class="literal">MERGE</code> algorithm so that other
      clients that use the view are not blocked as long.
    </p><p>
      A view algorithm can be <code class="literal">UNDEFINED</code> for three
      reasons:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          No <code class="literal">ALGORITHM</code> clause is present in the
          <a class="link" href="sql-syntax.html#create-view" title="13.1.21 CREATE VIEW Syntax"><code class="literal">CREATE VIEW</code></a> statement.
        </p></li><li class="listitem"><p>
          The <a class="link" href="sql-syntax.html#create-view" title="13.1.21 CREATE VIEW Syntax"><code class="literal">CREATE VIEW</code></a> statement has
          an explicit <code class="literal">ALGORITHM = UNDEFINED</code> clause.
        </p></li><li class="listitem"><p>
          <code class="literal">ALGORITHM = MERGE</code> is specified for a view
          that can be processed only with a temporary table. In this
          case, MySQL generates a warning and sets the algorithm to
          <code class="literal">UNDEFINED</code>.
</p></li></ul>
</div>
<p>
      As mentioned earlier, <code class="literal">MERGE</code> is handled by
      merging corresponding parts of a view definition into the
      statement that refers to the view. The following examples briefly
      illustrate how the <code class="literal">MERGE</code> algorithm works. The
      examples assume that there is a view <code class="literal">v_merge</code>
      that has this definition:
    </p><pre data-lang="sql" class="programlisting">
CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t WHERE c3 &gt; 100;
</pre><p>
      Example 1: Suppose that we issue this statement:
    </p><pre data-lang="sql" class="programlisting">
SELECT * FROM v_merge;
</pre><p>
      MySQL handles the statement as follows:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          <code class="literal">v_merge</code> becomes <code class="literal">t</code>
        </p></li><li class="listitem"><p>
          <code class="literal">*</code> becomes <code class="literal">vc1, vc2</code>,
          which corresponds to <code class="literal">c1, c2</code>
        </p></li><li class="listitem"><p>
          The view <code class="literal">WHERE</code> clause is added
</p></li></ul>
</div>
<p>
      The resulting statement to be executed becomes:
    </p><pre data-lang="sql" class="programlisting">
SELECT c1, c2 FROM t WHERE c3 &gt; 100;
</pre><p>
      Example 2: Suppose that we issue this statement:
    </p><pre data-lang="sql" class="programlisting">
SELECT * FROM v_merge WHERE vc1 &lt; 100;
</pre><p>
      This statement is handled similarly to the previous one, except
      that <code class="literal">vc1 &lt; 100</code> becomes <code class="literal">c1 &lt;
      100</code> and the view <code class="literal">WHERE</code> clause is
      added to the statement <code class="literal">WHERE</code> clause using an
      <a class="link" href="functions.html#operator_and"><code class="literal">AND</code></a> connective (and parentheses are
      added to make sure the parts of the clause are executed with
      correct precedence). The resulting statement to be executed
      becomes:
    </p><pre data-lang="sql" class="programlisting">
SELECT c1, c2 FROM t WHERE (c3 &gt; 100) AND (c1 &lt; 100);
</pre><p>
      Effectively, the statement to be executed has a
      <code class="literal">WHERE</code> clause of this form:
    </p><pre data-lang="sql" class="programlisting">
WHERE (select WHERE) AND (view WHERE)
</pre><p>
      If the <code class="literal">MERGE</code> algorithm cannot be used, a
      temporary table must be used instead. Constructs that prevent
      merging are the same as those that prevent merging in derived
      tables. Examples are <code class="literal">SELECT DISTINCT</code> or
      <code class="literal">LIMIT</code> in the subquery. For details, see
      <a class="xref" href="optimization.html#derived-table-optimization" title="8.2.2.3 Optimizing Derived Tables and View References">Section 8.2.2.3, “Optimizing Derived Tables and View References”</a>.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="view-updatability"></a>23.5.3 Updatable and Insertable Views</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm139684637565616"></a><a class="indexterm" name="idm139684637564576"></a><a class="indexterm" name="idm139684637563088"></a><a class="indexterm" name="idm139684637561600"></a><p>
      Some views are updatable and references to them can be used to
      specify tables to be updated in data change statements. That is,
      you can use them in statements such as
      <a class="link" href="sql-syntax.html#update" title="13.2.11 UPDATE Syntax"><code class="literal">UPDATE</code></a>,
      <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a>, or
      <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a> to update the contents of
      the underlying table. Derived tables can also be specified in
      multiple-table <a class="link" href="sql-syntax.html#update" title="13.2.11 UPDATE Syntax"><code class="literal">UPDATE</code></a> and
      <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a> statements, but can only be
      used for reading data to specify rows to be updated or deleted.
      Generally, the view references must be updatable, meaning that
      they may be merged and not materialized. Composite views have more
      complex rules.
    </p><p>
      For a view to be updatable, there must be a one-to-one
      relationship between the rows in the view and the rows in the
      underlying table. There are also certain other constructs that
      make a view nonupdatable. To be more specific, a view is not
      updatable if it contains any of the following:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Aggregate functions (<a class="link" href="functions.html#function_sum"><code class="literal">SUM()</code></a>,
          <a class="link" href="functions.html#function_min"><code class="literal">MIN()</code></a>,
          <a class="link" href="functions.html#function_max"><code class="literal">MAX()</code></a>,
          <a class="link" href="functions.html#function_count"><code class="literal">COUNT()</code></a>, and so forth)
        </p></li><li class="listitem"><p>
          <code class="literal">DISTINCT</code>
        </p></li><li class="listitem"><p>
          <code class="literal">GROUP BY</code>
        </p></li><li class="listitem"><p>
          <code class="literal">HAVING</code>
        </p></li><li class="listitem"><p>
          <a class="link" href="sql-syntax.html#union" title="13.2.9.3 UNION Syntax"><code class="literal">UNION</code></a> or
          <a class="link" href="sql-syntax.html#union" title="13.2.9.3 UNION Syntax"><code class="literal">UNION ALL</code></a>
        </p></li><li class="listitem"><p>
          Subquery in the select list
        </p><p>
          Before MySQL 5.7.11, subqueries in the select list fail for
          <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a>, but are okay for
          <a class="link" href="sql-syntax.html#update" title="13.2.11 UPDATE Syntax"><code class="literal">UPDATE</code></a>,
          <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a>. As of MySQL 5.7.11,
          that is still true for nondependent subqueries. For dependent
          subqueries in the select list, no data change statements are
          permitted.
        </p></li><li class="listitem"><p>
          Certain joins (see additional join discussion later in this
          section)
        </p></li><li class="listitem"><p>
          Reference to nonupdatable view in the <code class="literal">FROM</code>
          clause
        </p></li><li class="listitem"><p>
          Subquery in the <code class="literal">WHERE</code> clause that refers to
          a table in the <code class="literal">FROM</code> clause
        </p></li><li class="listitem"><p>
          Refers only to literal values (in this case, there is no
          underlying table to update)
        </p></li><li class="listitem"><p>
          <code class="literal">ALGORITHM = TEMPTABLE</code> (use of a temporary
          table always makes a view nonupdatable)
        </p></li><li class="listitem"><p>
          Multiple references to any column of a base table (fails for
          <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a>, okay for
          <a class="link" href="sql-syntax.html#update" title="13.2.11 UPDATE Syntax"><code class="literal">UPDATE</code></a>,
          <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a>)
</p></li></ul>
</div>
<a class="indexterm" name="idm139684637521632"></a><p>
      A generated column in a view is considered updatable because it is
      possible to assign to it. However, if such a column is updated
      explicitly, the only permitted value is
      <code class="literal">DEFAULT</code>. For information about generated
      columns, see <a class="xref" href="sql-syntax.html#create-table-generated-columns" title="13.1.18.8 CREATE TABLE and Generated Columns">Section 13.1.18.8, “CREATE TABLE and Generated Columns”</a>.
    </p><p>
      It is sometimes possible for a multiple-table view to be
      updatable, assuming that it can be processed with the
      <code class="literal">MERGE</code> algorithm. For this to work, the view
      must use an inner join (not an outer join or a
      <a class="link" href="sql-syntax.html#union" title="13.2.9.3 UNION Syntax"><code class="literal">UNION</code></a>). Also, only a single table
      in the view definition can be updated, so the
      <code class="literal">SET</code> clause must name only columns from one of
      the tables in the view. Views that use
      <a class="link" href="sql-syntax.html#union" title="13.2.9.3 UNION Syntax"><code class="literal">UNION ALL</code></a> are not
      permitted even though they might be theoretically updatable.
    </p><p>
      With respect to insertability (being updatable with
      <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a> statements), an updatable
      view is insertable if it also satisfies these additional
      requirements for the view columns:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          There must be no duplicate view column names.
        </p></li><li class="listitem"><p>
          The view must contain all columns in the base table that do
          not have a default value.
        </p></li><li class="listitem"><p>
          The view columns must be simple column references. They must
          not be expressions, such as these:
        </p><pre data-lang="sql" class="programlisting">
3.14159
col1 + 3
UPPER(col2)
col3 / col4
(<em class="replaceable"><code>subquery</code></em>)
</pre></li></ul>
</div>
<p>
      MySQL sets a flag, called the view updatability flag, at
      <a class="link" href="sql-syntax.html#create-view" title="13.1.21 CREATE VIEW Syntax"><code class="literal">CREATE VIEW</code></a> time. The flag is set
      to <code class="literal">YES</code> (true) if
      <a class="link" href="sql-syntax.html#update" title="13.2.11 UPDATE Syntax"><code class="literal">UPDATE</code></a> and
      <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a> (and similar operations) are
      legal for the view. Otherwise, the flag is set to
      <code class="literal">NO</code> (false). The <code class="literal">IS_UPDATABLE</code>
      column in the
      <a class="link" href="information-schema.html#views-table" title="24.31 The INFORMATION_SCHEMA VIEWS Table"><code class="literal">INFORMATION_SCHEMA.VIEWS</code></a> table
      displays the status of this flag.
    </p><p>
      If a view is not updatable, statements such
      <a class="link" href="sql-syntax.html#update" title="13.2.11 UPDATE Syntax"><code class="literal">UPDATE</code></a>,
      <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a>, and
      <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a> are illegal and are
      rejected. (Even if a view is updatable, it might not be possible
      to insert into it, as described elsewhere in this section.)
    </p><p>
      The <code class="literal">IS_UPDATABLE</code> flag may be unreliable if a
      view depends on one or more other views, and one of these
      underlying views is updated. Regardless of the
      <code class="literal">IS_UPDATABLE</code> value, the server keeps track of
      the updatability of a view and correctly rejects data change
      operations to views that are not updatable. If the
      <code class="literal">IS_UPDATABLE</code> value for a view has become
      inaccurate to due to changes to underlying views, the value can be
      updated by deleting and re-creating the view.
    </p><p>
      The updatability of views may be affected by the value of the
      <a class="link" href="server-administration.html#sysvar_updatable_views_with_limit"><code class="literal">updatable_views_with_limit</code></a> system
      variable. See <a class="xref" href="server-administration.html#server-system-variables" title="5.1.7 Server System Variables">Section 5.1.7, “Server System Variables”</a>.
    </p><p>
      For the following discussion, suppose that these tables and views
      exist:
    </p><pre data-lang="sql" class="programlisting">
CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (c INTEGER);
CREATE VIEW vmat AS SELECT SUM(x) AS s FROM t1;
CREATE VIEW vup AS SELECT * FROM t2;
CREATE VIEW vjoin AS SELECT * FROM vmat JOIN vup ON vmat.s=vup.c;
</pre><p>
      <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a>,
      <a class="link" href="sql-syntax.html#update" title="13.2.11 UPDATE Syntax"><code class="literal">UPDATE</code></a>, and
      <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a> statements are permitted as
      follows:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a>: The insert table of an
          <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a> statement may be a view
          reference that is merged. If the view is a join view, all
          components of the view must be updatable (not materialized).
          For a multiple-table updatable view,
          <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a> can work if it inserts
          into a single table.
        </p><p>
          This statement is invalid because one component of the join
          view is nonupdatable:
        </p><pre data-lang="sql" class="programlisting">
INSERT INTO vjoin (c) VALUES (1);
</pre><p>
          This statement is valid; the view contains no materialized
          components:
        </p><pre data-lang="sql" class="programlisting">
INSERT INTO vup (c) VALUES (1);
</pre></li><li class="listitem"><p>
          <a class="link" href="sql-syntax.html#update" title="13.2.11 UPDATE Syntax"><code class="literal">UPDATE</code></a>: The table or tables to
          be updated in an <a class="link" href="sql-syntax.html#update" title="13.2.11 UPDATE Syntax"><code class="literal">UPDATE</code></a>
          statement may be view references that are merged. If a view is
          a join view, at least one component of the view must be
          updatable (this differs from
          <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a>).
        </p><p>
          In a multiple-table <a class="link" href="sql-syntax.html#update" title="13.2.11 UPDATE Syntax"><code class="literal">UPDATE</code></a>
          statement, the updated table references of the statement must
          be base tables or updatable view references. Nonupdated table
          references may be materialized views or derived tables.
        </p><p>
          This statement is valid; column <code class="literal">c</code> is from
          the updatable part of the join view:
        </p><pre data-lang="sql" class="programlisting">
UPDATE vjoin SET c=c+1;
</pre><p>
          This statement is invalid; column <code class="literal">x</code> is from
          the nonupdatable part:
        </p><pre data-lang="sql" class="programlisting">
UPDATE vjoin SET x=x+1;
</pre><p>
          This statement is valid; the updated table reference of the
          multiple-table <a class="link" href="sql-syntax.html#update" title="13.2.11 UPDATE Syntax"><code class="literal">UPDATE</code></a> is an
          updatable view (<code class="literal">vup</code>):
        </p><pre data-lang="sql" class="programlisting">
UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...
SET c=c+1;
</pre><p>
          This statement is invalid; it tries to update a materialized
          derived table:
        </p><pre data-lang="sql" class="programlisting">
UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...
SET s=s+1;
</pre></li><li class="listitem"><p>
          <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a>: The table or tables to
          be deleted from in a <a class="link" href="sql-syntax.html#delete" title="13.2.2 DELETE Syntax"><code class="literal">DELETE</code></a>
          statement must be merged views. Join views are not allowed
          (this differs from <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a> and
          <a class="link" href="sql-syntax.html#update" title="13.2.11 UPDATE Syntax"><code class="literal">UPDATE</code></a>).
        </p><p>
          This statement is invalid because the view is a join view:
        </p><pre data-lang="sql" class="programlisting">
DELETE vjoin WHERE ...;
</pre><p>
          This statement is valid because the view is a merged
          (updatable) view:
        </p><pre data-lang="sql" class="programlisting">
DELETE vup WHERE ...;
</pre><p>
          This statement is valid because it deletes from a merged
          (updatable) view:
        </p><pre data-lang="sql" class="programlisting">
DELETE vup FROM vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...;
</pre></li></ul>
</div>
<p>
      Additional discussion and examples follow.
    </p><p>
      Earlier discussion in this section pointed out that a view is not
      insertable if not all columns are simple column references (for
      example, if it contains columns that are expressions or composite
      expressions). Although such a view is not insertable, it can be
      updatable if you update only columns that are not expressions.
      Consider this view:
    </p><pre data-lang="sql" class="programlisting">
CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;
</pre><p>
      This view is not insertable because <code class="literal">col2</code> is an
      expression. But it is updatable if the update does not try to
      update <code class="literal">col2</code>. This update is permissible:
    </p><pre data-lang="sql" class="programlisting">
UPDATE v SET col1 = 0;
</pre><p>
      This update is not permissible because it attempts to update an
      expression column:
    </p><pre data-lang="sql" class="programlisting">
UPDATE v SET col2 = 0;
</pre><p>
      If a table contains an <code class="literal">AUTO_INCREMENT</code> column,
      inserting into an insertable view on the table that does not
      include the <code class="literal">AUTO_INCREMENT</code> column does not
      change the value of
      <a class="link" href="functions.html#function_last-insert-id"><code class="literal">LAST_INSERT_ID()</code></a>, because the side
      effects of inserting default values into columns not part of the
      view should not be visible.
</p>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="view-check-option"></a>23.5.4 The View WITH CHECK OPTION Clause</h3>

</div>

</div>

</div>
<p>
      The <code class="literal">WITH CHECK OPTION</code> clause can be given for
      an updatable view to prevent inserts to rows for which the
      <code class="literal">WHERE</code> clause in the
      <em class="replaceable"><code>select_statement</code></em> is not true. It also
      prevents updates to rows for which the <code class="literal">WHERE</code>
      clause is true but the update would cause it to be not true (in
      other words, it prevents visible rows from being updated to
      nonvisible rows).
    </p><p>
      In a <code class="literal">WITH CHECK OPTION</code> clause for an updatable
      view, the <code class="literal">LOCAL</code> and <code class="literal">CASCADED</code>
      keywords determine the scope of check testing when the view is
      defined in terms of another view. When neither keyword is given,
      the default is <code class="literal">CASCADED</code>.
    </p><p>
      Before MySQL 5.7.6, <code class="literal">WITH CHECK OPTION</code> testing
      works like this:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          With <code class="literal">LOCAL</code>, the view
          <code class="literal">WHERE</code> clause is checked, but no underlying
          views are checked.
        </p></li><li class="listitem"><p>
          With <code class="literal">CASCADED</code>, the view
          <code class="literal">WHERE</code> clause is checked, then checking
          recurses to underlying views, adds <code class="literal">WITH CASCADED
          CHECK OPTION</code> to them (for purposes of the check;
          their definitions remain unchanged), and applies the same
          rules.
        </p></li><li class="listitem"><p>
          With no check option, the view <code class="literal">WHERE</code> clause
          is not checked, and no underlying views are checked.
</p></li></ul>
</div>
<p>
      As of MySQL 5.7.6, <code class="literal">WITH CHECK OPTION</code> testing is
      standard-compliant (with changed semantics from previously for
      <code class="literal">LOCAL</code> and no check clause):
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          With <code class="literal">LOCAL</code>, the view
          <code class="literal">WHERE</code> clause is checked, then checking
          recurses to underlying views and applies the same rules.
        </p></li><li class="listitem"><p>
          With <code class="literal">CASCADED</code>, the view
          <code class="literal">WHERE</code> clause is checked, then checking
          recurses to underlying views, adds <code class="literal">WITH CASCADED
          CHECK OPTION</code> to them (for purposes of the check;
          their definitions remain unchanged), and applies the same
          rules.
        </p></li><li class="listitem"><p>
          With no check option, the view <code class="literal">WHERE</code> clause
          is not checked, then checking recurses to underlying views,
          and applies the same rules.
</p></li></ul>
</div>
<p>
      Consider the definitions for the following table and set of views:
    </p><pre data-lang="sql" class="programlisting">
CREATE TABLE t1 (a INT);
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a &lt; 2
WITH CHECK OPTION;
CREATE VIEW v2 AS SELECT * FROM v1 WHERE a &gt; 0
WITH LOCAL CHECK OPTION;
CREATE VIEW v3 AS SELECT * FROM v1 WHERE a &gt; 0
WITH CASCADED CHECK OPTION;
</pre><p>
      Here the <code class="literal">v2</code> and <code class="literal">v3</code> views are
      defined in terms of another view, <code class="literal">v1</code>. Before
      MySQL 5.7.6, because <code class="literal">v2</code> has a
      <code class="literal">LOCAL</code> check option, inserts are tested only
      against the <code class="literal">v2</code> check. <code class="literal">v3</code> has
      a <code class="literal">CASCADED</code> check option, so inserts are tested
      not only against the <code class="literal">v3</code> check, but against
      those of underlying views. The following statements illustrate
      these differences:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>INSERT INTO v2 VALUES (2);</code></strong>
Query OK, 1 row affected (0.00 sec)
mysql&gt; <strong class="userinput"><code>INSERT INTO v3 VALUES (2);</code></strong>
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'
</pre><p>
      As of MySQL 5.7.6, the semantics for <code class="literal">LOCAL</code>
      differ from previously: Inserts for <code class="literal">v2</code> are
      checked against its <code class="literal">LOCAL</code> check option, then
      (unlike before 5.7.6), the check recurses to <code class="literal">v1</code>
      and the rules are applied again. The rules for
      <code class="literal">v1</code> cause a check failure. The check for
      <code class="literal">v3</code> fails as before:
    </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>INSERT INTO v2 VALUES (2);</code></strong>
ERROR 1369 (HY000): CHECK OPTION failed 'test.v2'
mysql&gt; <strong class="userinput"><code>INSERT INTO v3 VALUES (2);</code></strong>
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'
</pre>
</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a name="view-metadata"></a>23.5.5 View Metadata</h3>

</div>

</div>

</div>
<a class="indexterm" name="idm139684637394272"></a><a class="indexterm" name="idm139684637392784"></a><p>
      Metadata about views can be obtained as follows:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          Query the <a class="link" href="information-schema.html#views-table" title="24.31 The INFORMATION_SCHEMA VIEWS Table"><code class="literal">VIEWS</code></a> table of the
          <code class="literal">INFORMATION_SCHEMA</code> database. See
          <a class="xref" href="information-schema.html#views-table" title="24.31 The INFORMATION_SCHEMA VIEWS Table">Section 24.31, “The INFORMATION_SCHEMA VIEWS Table”</a>.
        </p></li><li class="listitem"><p>
          Use the <a class="link" href="sql-syntax.html#show-create-view" title="13.7.5.13 SHOW CREATE VIEW Syntax"><code class="literal">SHOW CREATE VIEW</code></a>
          statement. See <a class="xref" href="sql-syntax.html#show-create-view" title="13.7.5.13 SHOW CREATE VIEW Syntax">Section 13.7.5.13, “SHOW CREATE VIEW Syntax”</a>.
</p></li></ul>
</div>

</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="stored-programs-security"></a>23.6 Access Control for Stored Programs and Views</h2>

</div>

</div>

</div>
<a class="indexterm" name="idm139684637382800"></a><a class="indexterm" name="idm139684637381312"></a><a class="indexterm" name="idm139684637379824"></a><a class="indexterm" name="idm139684637378336"></a><a class="indexterm" name="idm139684637377264"></a><a class="indexterm" name="idm139684637375776"></a><p>
      Stored programs and views are defined prior to use and, when
      referenced, execute within a security context that determines
      their privileges. These privileges are controlled by their
      <code class="literal">DEFINER</code> attribute, and, if there is one, their
      <code class="literal">SQL SECURITY</code> characteristic.
    </p><p>
      All stored programs (procedures, functions, triggers, and events)
      and views can have a <code class="literal">DEFINER</code> attribute that
      names a MySQL account. If the <code class="literal">DEFINER</code> attribute
      is omitted from a stored program or view definition, the default
      account is the user who creates the object.
    </p><p>
      In addition, stored routines (procedures and functions) and views
      can have an <code class="literal">SQL SECURITY</code> characteristic with a
      value of <code class="literal">DEFINER</code> or <code class="literal">INVOKER</code>
      to specify whether the object executes in definer or invoker
      context. If the <code class="literal">SQL SECURITY</code> characteristic is
      omitted, the default is definer context.
    </p><p>
      Triggers and events have no <code class="literal">SQL SECURITY</code>
      characteristic and always execute in definer context. The server
      invokes these objects automatically as necessary, so there is no
      invoking user.
    </p><p>
      Definer and invoker security contexts differ as follows:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          A stored program or view that executes in definer security
          context executes with the privileges of the account named by
          its <code class="literal">DEFINER</code> attribute. These privileges may
          be entirely different from those of the invoking user. The
          invoker must have appropriate privileges to reference the
          object (for example, <a class="link" href="security.html#priv_execute"><code class="literal">EXECUTE</code></a> to
          call a stored procedure or
          <a class="link" href="security.html#priv_select"><code class="literal">SELECT</code></a> to select from a view),
          but when the object executes, the invoker's privileges are
          ignored and only the <code class="literal">DEFINER</code> account
          privileges matter. If this account has few privileges, the
          object is correspondingly limited in the operations it can
          perform. If the <code class="literal">DEFINER</code> account is highly
          privileged (such as a <code class="literal">root</code> account), the
          object can perform powerful operations <span class="emphasis"><em>no matter who
          invokes it.</em></span>
        </p></li><li class="listitem"><p>
          A stored routine or view that executes in invoker security
          context can perform only operations for which the invoker has
          privileges. The <code class="literal">DEFINER</code> attribute can be
          specified but has no effect for objects that execute in
          invoker context.
</p></li></ul>
</div>
<p>
      Consider the following stored procedure:
    </p><pre data-lang="sql" class="programlisting">
CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1()
SQL SECURITY DEFINER
BEGIN
  UPDATE t1 SET counter = counter + 1;
END;
</pre><p>
      Any user who has the <a class="link" href="security.html#priv_execute"><code class="literal">EXECUTE</code></a>
      privilege for <code class="literal">p1</code> can invoke it with a
      <a class="link" href="sql-syntax.html#call" title="13.2.1 CALL Syntax"><code class="literal">CALL</code></a> statement. However, when
      <code class="literal">p1</code> executes, it does so in definer security
      context and thus executes with the privileges of
      <code class="literal">'admin'@'localhost'</code>, the account named in the
      <code class="literal">DEFINER</code> attribute. This account must have the
      <a class="link" href="security.html#priv_execute"><code class="literal">EXECUTE</code></a> privilege for
      <code class="literal">p1</code> as well as the
      <a class="link" href="security.html#priv_update"><code class="literal">UPDATE</code></a> privilege for the table
      <code class="literal">t1</code>. Otherwise, the procedure fails.
    </p><p>
      Now consider this stored procedure, which is identical to
      <code class="literal">p1</code> except that its <code class="literal">SQL
      SECURITY</code> characteristic is <code class="literal">INVOKER</code>:
    </p><pre data-lang="sql" class="programlisting">
CREATE DEFINER = 'admin'@'localhost' PROCEDURE p2()
SQL SECURITY INVOKER
BEGIN
  UPDATE t1 SET counter = counter + 1;
END;
</pre><p>
      <code class="literal">p2</code>, unlike <code class="literal">p1</code>, executes in
      invoker security context. The <code class="literal">DEFINER</code> attribute
      is irrelevant and <code class="literal">p2</code> executes with the
      privileges of the invoking user. <code class="literal">p2</code> fails if
      the invoker lacks the <a class="link" href="security.html#priv_execute"><code class="literal">EXECUTE</code></a>
      privilege for <code class="literal">p2</code> or the
      <a class="link" href="security.html#priv_update"><code class="literal">UPDATE</code></a> privilege for the table
      <code class="literal">t1</code>.
    </p><p>
      MySQL uses the following rules to control which accounts a user
      can specify in an object <code class="literal">DEFINER</code> attribute:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          You can specify a <code class="literal">DEFINER</code> value other than
          your own account only if you have the
          <a class="link" href="security.html#priv_super"><code class="literal">SUPER</code></a> privilege.
        </p></li><li class="listitem"><p>
          If you do not have the <a class="link" href="security.html#priv_super"><code class="literal">SUPER</code></a>
          privilege, the only legal user value is your own account,
          either specified literally or by using
          <a class="link" href="functions.html#function_current-user"><code class="literal">CURRENT_USER</code></a>. You cannot set
          the definer to some other account.
</p></li></ul>
</div>
<p>
      To minimize the risk potential for stored program and view
      creation and use, follow these guidelines:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          For a stored routine or view, use <code class="literal">SQL SECURITY
          INVOKER</code> in the object definition when possible so
          that it can be used only by users with permissions appropriate
          for the operations performed by the object.
        </p></li><li class="listitem"><p>
          If you create definer-context stored programs or views while
          using an account that has the
          <a class="link" href="security.html#priv_super"><code class="literal">SUPER</code></a> privilege, specify an
          explicit <code class="literal">DEFINER</code> attribute that names an
          account possessing only the privileges required for the
          operations performed by the object. Specify a highly
          privileged <code class="literal">DEFINER</code> account only when
          absolutely necessary.
        </p></li><li class="listitem"><p>
          Administrators can prevent users from specifying highly
          privileged <code class="literal">DEFINER</code> accounts by not granting
          them the <a class="link" href="security.html#priv_super"><code class="literal">SUPER</code></a> privilege.
        </p></li><li class="listitem"><p>
          Definer-context objects should be written keeping in mind that
          they may be able to access data for which the invoking user
          has no privileges. In some cases, you can prevent reference to
          these objects by not granting unauthorized users particular
          privileges:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              A stored procedure or function cannot be referenced by a
              user who does not have the
              <a class="link" href="security.html#priv_execute"><code class="literal">EXECUTE</code></a> privilege for it.
            </p></li><li class="listitem"><p>
              A view cannot be referenced by a user who does not have
              the appropriate privilege for it
              (<a class="link" href="security.html#priv_select"><code class="literal">SELECT</code></a> to select from it,
              <a class="link" href="security.html#priv_insert"><code class="literal">INSERT</code></a> to insert into it,
              and so forth).
</p></li></ul>
</div>
<p>
          However, no such control exists for triggers because users do
          not reference them directly. A trigger always executes in
          definer context and is activated by access to the table with
          which it is associated, even ordinary table accesses by users
          with no special privileges. If the <code class="literal">DEFINER</code>
          account is highly privileged, the trigger can perform
          sensitive or dangerous operations. This remains true if the
          <a class="link" href="security.html#priv_super"><code class="literal">SUPER</code></a> and
          <a class="link" href="security.html#priv_trigger"><code class="literal">TRIGGER</code></a> privileges needed to
          create the trigger are revoked from the account of the user
          who created it. Administrators should be especially careful
          about granting users that combination of privileges.
</p></li></ul>
</div>

</div>
<div class="section">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a name="stored-programs-logging"></a>23.7 Binary Logging of Stored Programs</h2>

</div>

</div>

</div>
<p>
      The binary log contains information about SQL statements that
      modify database contents. This information is stored in the form
      of <span class="quote">“<span class="quote">events</span>”</span> that describe the modifications. The
      binary log has two important purposes:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          For replication, the binary log is used on master replication
          servers as a record of the statements to be sent to slave
          servers. The master server sends the events contained in its
          binary log to its slaves, which execute those events to make
          the same data changes that were made on the master. See
          <a class="xref" href="replication.html#replication-implementation" title="16.2 Replication Implementation">Section 16.2, “Replication Implementation”</a>.
        </p></li><li class="listitem"><p>
          Certain data recovery operations require use of the binary
          log. After a backup file has been restored, the events in the
          binary log that were recorded after the backup was made are
          re-executed. These events bring databases up to date from the
          point of the backup. See
          <a class="xref" href="backup-and-recovery.html#recovery-from-backups" title="7.3.2 Using Backups for Recovery">Section 7.3.2, “Using Backups for Recovery”</a>.
</p></li></ul>
</div>
<p>
      However, if logging occurs at the statement level, there are
      certain binary logging issues with respect to stored programs
      (stored procedures and functions, triggers, and events):
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          In some cases, a statement might affect different sets of rows
          on master and slave.
        </p></li><li class="listitem"><p>
          Replicated statements executed on a slave are processed by the
          slave SQL thread, which has full privileges. It is possible
          for a procedure to follow different execution paths on master
          and slave servers, so a user can write a routine containing a
          dangerous statement that will execute only on the slave where
          it is processed by a thread that has full privileges.
        </p></li><li class="listitem"><p>
          If a stored program that modifies data is nondeterministic, it
          is not repeatable. This can result in different data on master
          and slave, or cause restored data to differ from the original
          data.
</p></li></ul>
</div>
<p>
      This section describes how MySQL handles binary logging for stored
      programs. It states the current conditions that the implementation
      places on the use of stored programs, and what you can do to avoid
      logging problems. It also provides additional information about
      the reasons for these conditions.
    </p><p>
      In general, the issues described here result when binary logging
      occurs at the SQL statement level (statement-based binary
      logging). If you use row-based binary logging, the log contains
      changes made to individual rows as a result of executing SQL
      statements. When routines or triggers execute, row changes are
      logged, not the statements that make the changes. For stored
      procedures, this means that the
      <a class="link" href="sql-syntax.html#call" title="13.2.1 CALL Syntax"><code class="literal">CALL</code></a> statement is not
      logged. For stored functions, row changes made within the function
      are logged, not the function invocation. For triggers, row changes
      made by the trigger are logged. On the slave side, only the row
      changes are seen, not the stored program invocation.
    </p><p>
      Mixed format binary logging
      (<a class="link" href="replication.html#sysvar_binlog_format"><code class="literal">binlog_format=MIXED</code></a>)
      uses statement-based binary logging, except for cases where only
      row-based binary logging is guaranteed to lead to proper results.
      With mixed format, when a stored function, stored procedure,
      trigger, event, or prepared statement contains anything that is
      not safe for statement-based binary logging, the entire statement
      is marked as unsafe and logged in row format. The statements used
      to create and drop procedures, functions, triggers, and events are
      always safe, and are logged in statement format. For more
      information about row-based, mixed, and statement-based logging,
      and how safe and unsafe statements are determined, see
      <a class="xref" href="replication.html#replication-formats" title="16.2.1 Replication Formats">Section 16.2.1, “Replication Formats”</a>.
    </p><p>
      Unless noted otherwise, the remarks here assume that binary
      logging is enabled on the server (see
      <a class="xref" href="server-administration.html#binary-log" title="5.4.4 The Binary Log">Section 5.4.4, “The Binary Log”</a>.) If the binary log is not enabled,
      replication is not possible, nor is the binary log available for
      data recovery.
    </p><p>
      The conditions on the use of stored functions in MySQL can be
      summarized as follows. These conditions do not apply to stored
      procedures or Event Scheduler events and they do not apply unless
      binary logging is enabled.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          To create or alter a stored function, you must have the
          <a class="link" href="security.html#priv_super"><code class="literal">SUPER</code></a> privilege, in addition to
          the <a class="link" href="security.html#priv_create-routine"><code class="literal">CREATE ROUTINE</code></a> or
          <a class="link" href="security.html#priv_alter-routine"><code class="literal">ALTER ROUTINE</code></a> privilege that is
          normally required. (Depending on the
          <code class="literal">DEFINER</code> value in the function definition,
          <a class="link" href="security.html#priv_super"><code class="literal">SUPER</code></a> might be required
          regardless of whether binary logging is enabled. See
          <a class="xref" href="sql-syntax.html#create-procedure" title="13.1.16 CREATE PROCEDURE and CREATE FUNCTION Syntax">Section 13.1.16, “CREATE PROCEDURE and CREATE FUNCTION Syntax”</a>.)
        </p></li><li class="listitem"><p>
          When you create a stored function, you must declare either
          that it is deterministic or that it does not modify data.
          Otherwise, it may be unsafe for data recovery or replication.
        </p><p>
          By default, for a <a class="link" href="sql-syntax.html#create-function" title="13.1.13 CREATE FUNCTION Syntax"><code class="literal">CREATE
          FUNCTION</code></a> statement to be accepted, at least one of
          <code class="literal">DETERMINISTIC</code>, <code class="literal">NO SQL</code>,
          or <code class="literal">READS SQL DATA</code> must be specified
          explicitly. Otherwise an error occurs:
        </p><pre data-lang="none" class="programlisting">
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators
variable)
</pre><p>
          This function is deterministic (and does not modify data), so
          it is safe:
        </p><pre data-lang="sql" class="programlisting">
CREATE FUNCTION f1(i INT)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
  RETURN i;
END;
</pre><p>
          This function uses <a class="link" href="functions.html#function_uuid"><code class="literal">UUID()</code></a>,
          which is not deterministic, so the function also is not
          deterministic and is not safe:
        </p><pre data-lang="sql" class="programlisting">
CREATE FUNCTION f2()
RETURNS CHAR(36) CHARACTER SET utf8
BEGIN
  RETURN UUID();
END;
</pre><p>
          This function modifies data, so it may not be safe:
        </p><pre data-lang="sql" class="programlisting">
CREATE FUNCTION f3(p_id INT)
RETURNS INT
BEGIN
  UPDATE t SET modtime = NOW() WHERE id = p_id;
  RETURN ROW_COUNT();
END;
</pre><p>
          Assessment of the nature of a function is based on the
          <span class="quote">“<span class="quote">honesty</span>”</span> of the creator. MySQL does not check
          that a function declared <code class="literal">DETERMINISTIC</code> is
          free of statements that produce nondeterministic results.
        </p></li><li class="listitem"><p>
          When you attempt to execute a stored function, if
          <a class="link" href="replication.html#sysvar_binlog_format"><code class="literal">binlog_format=STATEMENT</code></a> is
          set, the <code class="literal">DETERMINISTIC</code> keyword must be
          specified in the function definition. If this is not the case,
          an error is generated and the function does not run, unless
          <a class="link" href="replication.html#sysvar_log_bin_trust_function_creators"><code class="literal">log_bin_trust_function_creators=1</code></a>
          is specified to override this check (see below). For recursive
          function calls, the <code class="literal">DETERMINISTIC</code> keyword
          is required on the outermost call only. If row-based or mixed
          binary logging is in use, the statement is accepted and
          replicated even if the function was defined without the
          <code class="literal">DETERMINISTIC</code> keyword.
        </p></li><li class="listitem"><p>
          Because MySQL does not check if a function really is
          deterministic at creation time, the invocation of a stored
          function with the <code class="literal">DETERMINISTIC</code> keyword
          might carry out an action that is unsafe for statement-based
          logging, or invoke a function or procedure containing unsafe
          statements. If this occurs when
          <a class="link" href="replication.html#sysvar_binlog_format"><code class="literal">binlog_format=STATEMENT</code></a>
          is set, a warning message is issued. If row-based or mixed
          binary logging is in use, no warning is issued, and the
          statement is replicated in row-based format.
        </p></li><li class="listitem"><p>
          To relax the preceding conditions on function creation (that
          you must have the <a class="link" href="security.html#priv_super"><code class="literal">SUPER</code></a>
          privilege and that a function must be declared deterministic
          or to not modify data), set the global
          <a class="link" href="replication.html#sysvar_log_bin_trust_function_creators"><code class="literal">log_bin_trust_function_creators</code></a>
          system variable to 1. By default, this variable has a value of
          0, but you can change it like this:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SET GLOBAL log_bin_trust_function_creators = 1;</code></strong>
</pre><p>
          You can also set this variable by using the
          <a class="link" href="replication.html#option_mysqld_log-bin-trust-function-creators"><code class="option">--log-bin-trust-function-creators=1</code></a>
          option when starting the server.
        </p><p>
          If binary logging is not enabled,
          <a class="link" href="replication.html#sysvar_log_bin_trust_function_creators"><code class="literal">log_bin_trust_function_creators</code></a>
          does not apply. <a class="link" href="security.html#priv_super"><code class="literal">SUPER</code></a> is not
          required for function creation unless, as described
          previously, the <code class="literal">DEFINER</code> value in the
          function definition requires it.
        </p></li><li class="listitem"><p>
          For information about built-in functions that may be unsafe
          for replication (and thus cause stored functions that use them
          to be unsafe as well), see
          <a class="xref" href="replication.html#replication-features" title="16.4.1 Replication Features and Issues">Section 16.4.1, “Replication Features and Issues”</a>.
</p></li></ul>
</div>
<p>
      Triggers are similar to stored functions, so the preceding remarks
      regarding functions also apply to triggers with the following
      exception: <a class="link" href="sql-syntax.html#create-trigger" title="13.1.20 CREATE TRIGGER Syntax"><code class="literal">CREATE TRIGGER</code></a> does not
      have an optional <code class="literal">DETERMINISTIC</code> characteristic,
      so triggers are assumed to be always deterministic. However, this
      assumption might be invalid in some cases. For example, the
      <a class="link" href="functions.html#function_uuid"><code class="literal">UUID()</code></a> function is nondeterministic
      (and does not replicate). Be careful about using such functions in
      triggers.
    </p><p>
      Triggers can update tables, so error messages similar to those for
      stored functions occur with <a class="link" href="sql-syntax.html#create-trigger" title="13.1.20 CREATE TRIGGER Syntax"><code class="literal">CREATE
      TRIGGER</code></a> if you do not have the required privileges. On
      the slave side, the slave uses the trigger
      <code class="literal">DEFINER</code> attribute to determine which user is
      considered to be the creator of the trigger.
    </p><p>
      The rest of this section provides additional detail about the
      logging implementation and its implications. You need not read it
      unless you are interested in the background on the rationale for
      the current logging-related conditions on stored routine use. This
      discussion applies only for statement-based logging, and not for
      row-based logging, with the exception of the first item:
      <code class="literal">CREATE</code> and <code class="literal">DROP</code> statements
      are logged as statements regardless of the logging mode.
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
          The server writes <a class="link" href="sql-syntax.html#create-event" title="13.1.12 CREATE EVENT Syntax"><code class="literal">CREATE EVENT</code></a>,
          <a class="link" href="sql-syntax.html#create-procedure" title="13.1.16 CREATE PROCEDURE and CREATE FUNCTION Syntax"><code class="literal">CREATE PROCEDURE</code></a>,
          <a class="link" href="sql-syntax.html#create-function" title="13.1.13 CREATE FUNCTION Syntax"><code class="literal">CREATE FUNCTION</code></a>,
          <a class="link" href="sql-syntax.html#alter-event" title="13.1.2 ALTER EVENT Syntax"><code class="literal">ALTER EVENT</code></a>,
          <a class="link" href="sql-syntax.html#alter-procedure" title="13.1.6 ALTER PROCEDURE Syntax"><code class="literal">ALTER PROCEDURE</code></a>,
          <a class="link" href="sql-syntax.html#alter-function" title="13.1.3 ALTER FUNCTION Syntax"><code class="literal">ALTER FUNCTION</code></a>,
          <a class="link" href="sql-syntax.html#drop-event" title="13.1.23 DROP EVENT Syntax"><code class="literal">DROP EVENT</code></a>,
          <a class="link" href="sql-syntax.html#drop-procedure" title="13.1.27 DROP PROCEDURE and DROP FUNCTION Syntax"><code class="literal">DROP PROCEDURE</code></a>, and
          <a class="link" href="sql-syntax.html#drop-function" title="13.1.24 DROP FUNCTION Syntax"><code class="literal">DROP FUNCTION</code></a> statements to the
          binary log.
        </p></li><li class="listitem"><p>
          A stored function invocation is logged as a
          <a class="link" href="sql-syntax.html#select" title="13.2.9 SELECT Syntax"><code class="literal">SELECT</code></a> statement if the
          function changes data and occurs within a statement that would
          not otherwise be logged. This prevents nonreplication of data
          changes that result from use of stored functions in nonlogged
          statements. For example, <a class="link" href="sql-syntax.html#select" title="13.2.9 SELECT Syntax"><code class="literal">SELECT</code></a>
          statements are not written to the binary log, but a
          <a class="link" href="sql-syntax.html#select" title="13.2.9 SELECT Syntax"><code class="literal">SELECT</code></a> might invoke a stored
          function that makes changes. To handle this, a <code class="literal">SELECT
          <em class="replaceable"><code>func_name</code></em>()</code> statement is
          written to the binary log when the given function makes a
          change. Suppose that the following statements are executed on
          the master:
        </p><pre data-lang="sql" class="programlisting">
CREATE FUNCTION f1(a INT) RETURNS INT
BEGIN
  IF (a &lt; 3) THEN
    INSERT INTO t2 VALUES (a);
  END IF;
  RETURN 0;
END;

CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2),(3);

SELECT f1(a) FROM t1;
</pre><p>
          When the <a class="link" href="sql-syntax.html#select" title="13.2.9 SELECT Syntax"><code class="literal">SELECT</code></a> statement
          executes, the function <code class="literal">f1()</code> is invoked
          three times. Two of those invocations insert a row, and MySQL
          logs a <a class="link" href="sql-syntax.html#select" title="13.2.9 SELECT Syntax"><code class="literal">SELECT</code></a> statement for
          each of them. That is, MySQL writes the following statements
          to the binary log:
        </p><pre data-lang="sql" class="programlisting">
SELECT f1(1);
SELECT f1(2);
</pre><p>
          The server also logs a <a class="link" href="sql-syntax.html#select" title="13.2.9 SELECT Syntax"><code class="literal">SELECT</code></a>
          statement for a stored function invocation when the function
          invokes a stored procedure that causes an error. In this case,
          the server writes the <a class="link" href="sql-syntax.html#select" title="13.2.9 SELECT Syntax"><code class="literal">SELECT</code></a>
          statement to the log along with the expected error code. On
          the slave, if the same error occurs, that is the expected
          result and replication continues. Otherwise, replication
          stops.
        </p></li><li class="listitem"><p>
          Logging stored function invocations rather than the statements
          executed by a function has a security implication for
          replication, which arises from two factors:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              It is possible for a function to follow different
              execution paths on master and slave servers.
            </p></li><li class="listitem"><p>
              Statements executed on a slave are processed by the slave
              SQL thread which has full privileges.
</p></li></ul>
</div>
<p>
          The implication is that although a user must have the
          <a class="link" href="security.html#priv_create-routine"><code class="literal">CREATE ROUTINE</code></a> privilege to
          create a function, the user can write a function containing a
          dangerous statement that will execute only on the slave where
          it is processed by a thread that has full privileges. For
          example, if the master and slave servers have server ID values
          of 1 and 2, respectively, a user on the master server could
          create and invoke an unsafe function
          <code class="literal">unsafe_func()</code> as follows:
        </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>delimiter //</code></strong>
mysql&gt; <strong class="userinput"><code>CREATE FUNCTION unsafe_func () RETURNS INT</code></strong>
    -&gt; <strong class="userinput"><code>BEGIN</code></strong>
    -&gt;   <strong class="userinput"><code>IF @@server_id=2 THEN <em class="replaceable"><code>dangerous_statement</code></em>; END IF;</code></strong>
    -&gt;   <strong class="userinput"><code>RETURN 1;</code></strong>
    -&gt; <strong class="userinput"><code>END;</code></strong>
    -&gt; <strong class="userinput"><code>//</code></strong>
mysql&gt; <strong class="userinput"><code>delimiter ;</code></strong>
mysql&gt; <strong class="userinput"><code>INSERT INTO t VALUES(unsafe_func());</code></strong>
</pre><p>
          The <a class="link" href="sql-syntax.html#create-function" title="13.1.13 CREATE FUNCTION Syntax"><code class="literal">CREATE FUNCTION</code></a> and
          <a class="link" href="sql-syntax.html#insert" title="13.2.5 INSERT Syntax"><code class="literal">INSERT</code></a> statements are written
          to the binary log, so the slave will execute them. Because the
          slave SQL thread has full privileges, it will execute the
          dangerous statement. Thus, the function invocation has
          different effects on the master and slave and is not
          replication-safe.
        </p><p>
          To guard against this danger for servers that have binary
          logging enabled, stored function creators must have the
          <a class="link" href="security.html#priv_super"><code class="literal">SUPER</code></a> privilege, in addition to
          the usual <a class="link" href="security.html#priv_create-routine"><code class="literal">CREATE ROUTINE</code></a>
          privilege that is required. Similarly, to use
          <a class="link" href="sql-syntax.html#alter-function" title="13.1.3 ALTER FUNCTION Syntax"><code class="literal">ALTER FUNCTION</code></a>, you must have
          the <a class="link" href="security.html#priv_super"><code class="literal">SUPER</code></a> privilege in addition
          to the <a class="link" href="security.html#priv_alter-routine"><code class="literal">ALTER ROUTINE</code></a> privilege.
          Without the <a class="link" href="security.html#priv_super"><code class="literal">SUPER</code></a> privilege, an
          error will occur:
        </p><pre data-lang="none" class="programlisting">
ERROR 1419 (HY000): You do not have the SUPER privilege and
binary logging is enabled (you *might* want to use the less safe
log_bin_trust_function_creators variable)
</pre><p>
          If you do not want to require function creators to have the
          <a class="link" href="security.html#priv_super"><code class="literal">SUPER</code></a> privilege (for example,
          if all users with the <a class="link" href="security.html#priv_create-routine"><code class="literal">CREATE
          ROUTINE</code></a> privilege on your system are experienced
          application developers), set the global
          <a class="link" href="replication.html#sysvar_log_bin_trust_function_creators"><code class="literal">log_bin_trust_function_creators</code></a>
          system variable to 1. You can also set this variable by using
          the
          <a class="link" href="replication.html#option_mysqld_log-bin-trust-function-creators"><code class="option">--log-bin-trust-function-creators=1</code></a>
          option when starting the server. If binary logging is not
          enabled,
          <a class="link" href="replication.html#sysvar_log_bin_trust_function_creators"><code class="literal">log_bin_trust_function_creators</code></a>
          does not apply. <a class="link" href="security.html#priv_super"><code class="literal">SUPER</code></a> is not
          required for function creation unless, as described
          previously, the <code class="literal">DEFINER</code> value in the
          function definition requires it.
        </p></li><li class="listitem"><p>
          If a function that performs updates is nondeterministic, it is
          not repeatable. This can have two undesirable effects:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              It will make a slave different from the master.
            </p></li><li class="listitem"><p>
              Restored data will be different from the original data.
</p></li></ul>
</div>
<p>
          To deal with these problems, MySQL enforces the following
          requirement: On a master server, creation and alteration of a
          function is refused unless you declare the function to be
          deterministic or to not modify data. Two sets of function
          characteristics apply here:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              The <code class="literal">DETERMINISTIC</code> and <code class="literal">NOT
              DETERMINISTIC</code> characteristics indicate whether a
              function always produces the same result for given inputs.
              The default is <code class="literal">NOT DETERMINISTIC</code> if
              neither characteristic is given. To declare that a
              function is deterministic, you must specify
              <code class="literal">DETERMINISTIC</code> explicitly.
            </p></li><li class="listitem"><p>
              The <code class="literal">CONTAINS SQL</code>, <code class="literal">NO
              SQL</code>, <code class="literal">READS SQL DATA</code>, and
              <code class="literal">MODIFIES SQL DATA</code> characteristics
              provide information about whether the function reads or
              writes data. Either <code class="literal">NO SQL</code> or
              <code class="literal">READS SQL DATA</code> indicates that a
              function does not change data, but you must specify one of
              these explicitly because the default is <code class="literal">CONTAINS
              SQL</code> if no characteristic is given.
</p></li></ul>
</div>
<p>
          By default, for a <a class="link" href="sql-syntax.html#create-function" title="13.1.13 CREATE FUNCTION Syntax"><code class="literal">CREATE
          FUNCTION</code></a> statement to be accepted, at least one of
          <code class="literal">DETERMINISTIC</code>, <code class="literal">NO SQL</code>,
          or <code class="literal">READS SQL DATA</code> must be specified
          explicitly. Otherwise an error occurs:
        </p><pre data-lang="none" class="programlisting">
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators
variable)
</pre><p>
          If you set
          <a class="link" href="replication.html#sysvar_log_bin_trust_function_creators"><code class="literal">log_bin_trust_function_creators</code></a>
          to 1, the requirement that functions be deterministic or not
          modify data is dropped.
        </p></li><li class="listitem"><p>
          Stored procedure calls are logged at the statement level
          rather than at the <a class="link" href="sql-syntax.html#call" title="13.2.1 CALL Syntax"><code class="literal">CALL</code></a> level.
          That is, the server does not log the
          <a class="link" href="sql-syntax.html#call" title="13.2.1 CALL Syntax"><code class="literal">CALL</code></a> statement, it logs those
          statements within the procedure that actually execute. As a
          result, the same changes that occur on the master will be
          observed on slave servers. This prevents problems that could
          result from a procedure having different execution paths on
          different machines.
        </p><p>
          In general, statements executed within a stored procedure are
          written to the binary log using the same rules that would
          apply were the statements to be executed in standalone
          fashion. Some special care is taken when logging procedure
          statements because statement execution within procedures is
          not quite the same as in nonprocedure context:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
              <a class="indexterm" name="idm139684637143440"></a>

              A statement to be logged might contain references to local
              procedure variables. These variables do not exist outside
              of stored procedure context, so a statement that refers to
              such a variable cannot be logged literally. Instead, each
              reference to a local variable is replaced by this
              construct for logging purposes:
            </p><pre data-lang="sql" class="programlisting">
NAME_CONST(<em class="replaceable"><code>var_name</code></em>, <em class="replaceable"><code>var_value</code></em>)
</pre><p>
              <em class="replaceable"><code>var_name</code></em> is the local variable
              name, and <em class="replaceable"><code>var_value</code></em> is a
              constant indicating the value that the variable has at the
              time the statement is logged.
              <a class="link" href="functions.html#function_name-const"><code class="literal">NAME_CONST()</code></a> has a value of
              <em class="replaceable"><code>var_value</code></em>, and a
              <span class="quote">“<span class="quote">name</span>”</span> of
              <em class="replaceable"><code>var_name</code></em>. Thus, if you invoke
              this function directly, you get a result like this:
            </p><pre data-lang="sql" class="programlisting">
mysql&gt; <strong class="userinput"><code>SELECT NAME_CONST('myname', 14);</code></strong>
+--------+
| myname |
+--------+
|     14 |
+--------+
</pre><p>
              <a class="link" href="functions.html#function_name-const"><code class="literal">NAME_CONST()</code></a> enables a
              logged standalone statement to be executed on a slave with
              the same effect as the original statement that was
              executed on the master within a stored procedure.
            </p><p>
              The use of <a class="link" href="functions.html#function_name-const"><code class="literal">NAME_CONST()</code></a> can
              result in a problem for
              <a class="link" href="sql-syntax.html#create-table" title="13.1.18 CREATE TABLE Syntax"><code class="literal">CREATE TABLE
              ... SELECT</code></a> statements when the source column
              expressions refer to local variables. Converting these
              references to <a class="link" href="functions.html#function_name-const"><code class="literal">NAME_CONST()</code></a>
              expressions can result in column names that are different
              on the master and slave servers, or names that are too
              long to be legal column identifiers. A workaround is to
              supply aliases for columns that refer to local variables.
              Consider this statement when <code class="literal">myvar</code> has
              a value of 1:
            </p><pre data-lang="sql" class="programlisting">
CREATE TABLE t1 SELECT myvar;
</pre><p>
              That will be rewritten as follows:
            </p><pre data-lang="sql" class="programlisting">
CREATE TABLE t1 SELECT NAME_CONST(myvar, 1);
</pre><p>
              To ensure that the master and slave tables have the same
              column names, write the statement like this:
            </p><pre data-lang="sql" class="programlisting">
CREATE TABLE t1 SELECT myvar AS myvar;
</pre><p>
              The rewritten statement becomes:
            </p><pre data-lang="sql" class="programlisting">
CREATE TABLE t1 SELECT NAME_CONST(myvar, 1) AS myvar;
</pre></li><li class="listitem"><p>
              A statement to be logged might contain references to
              user-defined variables. To handle this, MySQL writes a
              <a class="link" href="sql-syntax.html#set-variable" title="13.7.4.1 SET Syntax for Variable Assignment"><code class="literal">SET</code></a>
              statement to the binary log to make sure that the variable
              exists on the slave with the same value as on the master.
              For example, if a statement refers to a variable
              <code class="literal">@my_var</code>, that statement will be
              preceded in the binary log by the following statement,
              where <em class="replaceable"><code>value</code></em> is the value of
              <code class="literal">@my_var</code> on the master:
            </p><pre data-lang="sql" class="programlisting">
SET @my_var = <em class="replaceable"><code>value</code></em>;
</pre></li><li class="listitem"><p>
              Procedure calls can occur within a committed or
              rolled-back transaction. Transactional context is
              accounted for so that the transactional aspects of
              procedure execution are replicated correctly. That is, the
              server logs those statements within the procedure that
              actually execute and modify data, and also logs
              <a class="link" href="sql-syntax.html#commit" title="13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax"><code class="literal">BEGIN</code></a>,
              <a class="link" href="sql-syntax.html#commit" title="13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax"><code class="literal">COMMIT</code></a>, and
              <a class="link" href="sql-syntax.html#commit" title="13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax"><code class="literal">ROLLBACK</code></a>
              statements as necessary. For example, if a procedure
              updates only transactional tables and is executed within a
              transaction that is rolled back, those updates are not
              logged. If the procedure occurs within a committed
              transaction,
              <a class="link" href="sql-syntax.html#commit" title="13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax"><code class="literal">BEGIN</code></a>
              and <a class="link" href="sql-syntax.html#commit" title="13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax"><code class="literal">COMMIT</code></a> statements are
              logged with the updates. For a procedure that executes
              within a rolled-back transaction, its statements are
              logged using the same rules that would apply if the
              statements were executed in standalone fashion:
</p>
<div class="itemizedlist">
<ul class="itemizedlist" style="list-style-type: square; "><li class="listitem"><p>
                  Updates to transactional tables are not logged.
                </p></li><li class="listitem"><p>
                  Updates to nontransactional tables are logged because
                  rollback does not cancel them.
                </p></li><li class="listitem"><p>
                  Updates to a mix of transactional and nontransactional
                  tables are logged surrounded by
                  <a class="link" href="sql-syntax.html#commit" title="13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax"><code class="literal">BEGIN</code></a>
                  and
                  <a class="link" href="sql-syntax.html#commit" title="13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax"><code class="literal">ROLLBACK</code></a>
                  so that slaves will make the same changes and
                  rollbacks as on the master.
</p></li></ul>
</div>
</li></ul>
</div>
</li><li class="listitem"><p>
          A stored procedure call is <span class="emphasis"><em>not</em></span> written to
          the binary log at the statement level if the procedure is
          invoked from within a stored function. In that case, the only
          thing logged is the statement that invokes the function (if it
          occurs within a statement that is logged) or a
          <a class="link" href="sql-syntax.html#do" title="13.2.3 DO Syntax"><code class="literal">DO</code></a> statement (if it occurs
          within a statement that is not logged). For this reason, care
          should be exercised in the use of stored functions that invoke
          a procedure, even if the procedure is otherwise safe in
          itself.
</p></li></ul>
</div>

</div>

</div>
<div class="copyright-footer">

</div>
<div class="navfooter">
<hr>
<table width="100%" summary="Navigation footer">
<tr>
<td width="40%" align="left"><a accesskey="p" href="partitioning.html">Prev</a></td>
<td width="20%" align="center"><a accesskey="u" href="">Up</a></td>
<td width="40%" align="right"> <a accesskey="n" href="information-schema.html">Next</a></td>
</tr>
<tr>
<td width="40%" align="left" valign="top">Chapter 22 Partitioning</td>
<td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td>
<td width="40%" align="right" valign="top">Chapter 24 INFORMATION_SCHEMA Tables</td>
</tr>
</table>
</div>
</body>
</html>
